Re: Mutating tables

From: Karla Bocaletti <kbocalet_at_guate.net>
Date: 1996/01/14
Message-ID: <4dbjvl$4br_at_despina.neptune.com>#1/1

Hi Richard!

I've had a lot of troubles with mutating tables. Oracle doesn't let you modify or even select any record from the table until it has finished the updating (or inserting or deleting) so while executing the After Insert trigger it is still updating the table.

In your trigger, the exception is handling the mutating error, so you never get the error but it is still happening, that's why you don't get the updating.

I'm sending you 2 posible solutions, the first one is the easiest and only works if you don't need the updating AFTER the inserting. Use the second one only if you NEED the updating AFTER the inserting.

Solution 1:

Make the update in the before insert trigger.

create OR REPLACE trigger tI_attachee

        BEFORE insert
        on attachee
        FOR EACH ROW
begin
     :new.acu_stdld_key_i = -1

end;
/

Solution 2:

If you NEED the update AFTER the insert, you have to use statement triggers besides the row trigger.

You have to write a package. This package will help you to store the key values you need to update the table.

CREATE OR REPLACE PACKAGE attachee_pk as

   Kxtern_co_id_c    attachee.xtern_co_id_c%type;
   Kattachee_cd_id_c attachee.attachee_cd_id_c%type;
END attachee_pk;

/

This must be your row trigger:

create OR REPLACE trigger tI_attachee

        after insert
        on attachee
        FOR EACH ROW

begin

/* asign the key values of the row you need to update to the package variables you

    have declare in the package */
    attachee_pk.Kxtern_co_id_c := :old.xtern_co_id_c;     attachee_pk.Kattachee_cd_id_c := :old.attachee_cd_id_c;

end;
/

and your statement trigger:

CREATE OR REPLACE TRIGGER stmt_sI_attachee AFTER
INSERT
ON attachee
BEGIN
/* here you do the update to the table with the key values you've assigned to the

   package variables in the after insert row trigger */

     update attachee
     set acu_stdld_key_i = -1
     where attachee.xtern_co_id_c = attachee_pk.Kxtern_co_id_c and
           attachee.attachee_cd_id_c = attachee_pk.Kattachee_cd_id_c;

END stmt_sI_attachee;

This must resolve your problem. If you have any doubt about it, feel free to send me mail, I'll help you with anything I can.

Karla Received on Sun Jan 14 1996 - 00:00:00 CET