Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

    Unanswered: Problem with after update trigger

    I need to update two tables based on the values of two columns in a third table.

    Problem : when trigger is activated, table 3 is not updated to state where trigger is fired.

    This is what I did:
    create or replace trigger trig
    after update on table 3
    for each row
    when (table3.c1 in (val1,val2) and table3.c2 in (val3,val4))

    I have two cursors : cursor1 is used to fetch values when table3.c1 contains val1 and this data from cursor 1 is inserted into table1.

    cursor2 is used to fetch values when table3.c1 contains val2 and this data from cursor 2 is inserted into table2. one entry in cursor1 source table can have multiple associated entries in cursor 2 source table.

    table1 is like parent record and table 2 is like child records.

    The first mistake I made was mutating table and I fixed it.

    I still have the problem where table 3 update fails to happen ( a separate process - pro c handles the update) and my trigger has to update table 1 and table 2 which hold status data.

    I found that a separate process was inserting data into table 1 and table 2 and my trigger was failing on the insert as it was violating the primary key constraint. I am going to fix this and see if this fixes the issue.

    Could there be any other reason why the update on table 3 is failing ?

    Thanks in advance.

  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL

    Thumbs down Bad design.

    Quote Originally Posted by sanjayc
    Could there be any other reason why the update on table 3 is failing ?
    Seems to be a bad design issue.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jun 2004
    Liverpool, NY USA
    how can you update table 3 when you are using a trigger that is fired AFTER the update has already occurred. And on a row trigger you can ONLY update the row that is being updated by using the

    :new.col1 := other_value;

    and you must use a before update FOR EACH row trigger.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts