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

Udaya wrote:

> 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,
>
> 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.

A trigger can not modify records in its host table except the single specific record whose values are in the trigger as :OLD/:NEW.

Daniel A. Morgan Received on Thu Jul 26 2001 - 15:14:31 CDT