Mutating table inside trigger

From: Hans Börjesson <hansb_at_NOSPAMvendimo.com>
Date: Fri, 7 Mar 2003 12:43:13 +0100
Message-ID: <b4a0bf$1ea$1@yggdrasil.utfors.se>

Hi!

I have a before insert,update,delete trigger which put records inside a log table (this works OK). Now I have to add some more functionality to this trigger. I do this before I add the log record to the logpost.

1. If you insert a record (call this RecordA) with fieldA='A' then add three records (RecordB,RecordC,RecordD) in the same table with fieldA not equal to A (no circular adding). In these three records I add the key to the record RecordA.
2. If you update RecordA (fieldA='A') I will update RecordB,RecordC,RecordD (in the same table) using the key to RecordA which I stored in the insert trigger. Since these three records do not have fieldA='A' it should not be a circular trigger. I am fething only :new values to propagate these values from RecordA to RecordB,RecordC,RecordD.
3. If you delete RecordA (fieldA='A') you should delete the other three records using the key to RecordA (only refering to :OLD).

The insert is OK but I'm getting table is mutating and trigger may not see it error.

I have omitted some part to at least show you the ide. Oracle 9i is used.

CREATE OR REPLACE TRIGGER MY_TRIGGER_TR BEFORE INSERT OR DELETE OR UPDATE
ON MY_TABLE REFERENCING OLD AS OLDREC NEW AS NEWREC FOR EACH ROW DECLARE
 ---some variables here

IF INSERTING THEN
    IF (:newREC.IDCODKAT ='kat.001') THEN

• Do insert of RecordB, RecordC, RecordD into the same table
• using :newrec values. Also store key to this record in RecordB, RecordC, RecordD end IF ; --Do insert into log tables which I always do no matter value of
  :newREC.IDCODKAT
  END IF;

IF UPDATING THEN
    IF (:newREC.IDCODKAT ='kat.001') THEN

       --Update RecordB,RecordC,RecordD using :newREC values     end IF;
    --Do insert into log tables which I always do no matter value of
:newREC.IDCODKAT

end if;

IF DELETING THEN
    IF (:oldREC.IDCODKAT ='kat.001') THEN

        --Delete RecordB,RecordC,RecordD using :oldREC values     end IF;
    --Do insert into log tables which I always do no matter value of
:oldREC.IDCODKAT

END IF; END; I can get it to work if I follow the steps http://osi.oracle.com/~tkyte/Mutate/index.html

1. Creating a package that defines an array where I can store rowids.
2. A before trigger which "reset" the array
3. An after row level trigger which saves the rowid into the array
4. An efter trigger that loops the array and perform the action

but this seems to be like hunting ants with a elephant gun. Isn't there an easier solution (in Oracle9i)?

Regards
/Hans Börjesson Received on Fri Mar 07 2003 - 05:43:13 CST