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

    Unanswered: Problem with after update trigger

    Hi,
    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
    Location
    West Palm Beach, FL
    Posts
    2,713

    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
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    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
  •