Re: table mutating error - how do i get the :old, :new values?

In article <13108883.0107261102.590d35e_at_posting.google.com>, udayasangeetha_at_hotmail.com says...
>
>Hi,
>
>I am new to Oracle and I have recently started working on triggers. I
>have a trigger on update/delete on each row on table A, which (should)
>update(s) multiple records in the same table based on the :old and
>:new values of the record being updated, but I get the "table
>mutating" error. After reading few of the replies from this group for
>this "table mutating" error, I tried using the package to get around
>this problem and I was successful in getting the :old value while
>deleting and updating but could not get the :new value while updating.
>
>I saw the solutions for these 2 cases using packages,
>
>INSERT trigger or an UPDATE trigger where you only need access to the
>:new values
>DELETE trigger or an UPDATE trigger where you need to access the :old
>values
>
>But how do I get both :new and :old values in the update trigger?
>Here is the table structure,
>

just merge the two concepts together....

see
http://osi.oracle.com/~tkyte/Mutate/index.html

You would save the :new.rowid or :new.primary_key into a plsql table. You would save the :old.c1, :old.c2, ..., :old.cN into plsql tables...

process them in an AFTER trigger.

Don't forget to add a flag somewhere so they your triggers don't "fire" when YOU update the tables

>bb_relationship
>----------------------
>element_id number(8)
>linked_element_id number(8)
>has_child number(1)
>
>
>Here is the trigger which explains what it is supposed to do,
>----------------------------------------------------------------
>create or replace trigger change_has_child
> after update or delete on bb_relationship
>
>declare
> cnt number(2);
>begin
> SELECT count(*) INTO cnt
> FROM bb_relationship
> WHERE linked_element_id = :OLD.linked_element_id;
>
> IF UPDATING THEN
>
> UPDATE bb_relationship
> SET has_child = 1
> WHERE element_id = :NEW.linked_element_id
>
> IF cnt = 0 THEN
> UPDATE bb_relationship
> SET has_child = 0
> WHERE element_id = :OLD.linked_element_id
> END IF;
>
> ELSE /* deleting */
> IF cnt = 0 THEN
> UPDATE bb_relationship_old
> SET has_child = 0
> WHERE element_id = :OLD.linked_element_id
> END IF;
> END IF;
>end;
>-----------------------------------------------------------------------
>
>Any help would be greatly appreciated!
>
>Thanks in advance,
>Udaya.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp