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

From: Udaya <udayasangeetha_at_hotmail.com>
Date: 26 Jul 2001 12:01:29 -0700
Message-ID: <13108883.0107261101.10af30d5_at_posting.google.com>

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. Received on Thu Jul 26 2001 - 21:01:29 CEST