Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> table mutating error - how do i get the :old, :new values?
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
Here is the trigger which explains what it is supposed to do,
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;
Any help would be greatly appreciated!
Thanks in advance,
Udaya.
Received on Thu Jul 26 2001 - 14:02:01 CDT