Re: Mutating table inside trigger

"Hans Börjesson" wrote:

> 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

Have the trigger call a stored procedure that is an autonomous transaction, or, add an after-insert trigger to do the other inserts. One or both might work.

But a bigger issue here is what appears to be a design error. Why should inserting one record in a table cause other records to be inserted into the same table? Consider this in terms of normalization and business logic. For example why is it that the procedure inserting the original record isn't just adding the additional records too?

Daniel Morgan Received on Fri Mar 07 2003 - 10:10:10 CST