getting error ORA-4091 (mutating table), what is workaround while still enforcing RI

From: <jeffchirco_at_gmail.com>
Date: 29 Sep 2005 10:13:03 -0700
Message-ID: <1128013983.291277.85850_at_g43g2000cwa.googlegroups.com>

[Quoted] [Quoted] I have a child parent releationship going. And I want to enforce referential
integrity with triggers. When I update the parent table I want it to go down
and update the child. But if I update the child it needs to check to make sure
there is a matching parent.
So I have a trigger after udate on the parent table that goes and updates all
the childs.

create or replace trigger TU_LU_PARENT after UPDATE on LU_PARENT for each row
declare numrows INTEGER;
begin
if
:old.DBPARENTID <> :new.DBPARENTID

then
update LU_MAJOR
set
LU_MAJOR.DBPARENTID = :new.DBPARENTID
where
LU_MAJOR.DBPARENTID = :old.DBPARENTID;
end if;
end;

Then there is also a trigger after update on the child that checks the parent
table to see if a record exists. When this happens I get an ORA-4091 error
table is mutating. How do I get around this and still enforce RI.

create or replace trigger tU_LU_MAJOR after UPDATE on LU_MAJOR for each row
declare numrows INTEGER;
begin
select count(*) into numrows
from LU_PARENT
where
:new.DBPARENTID = LU_PARENT.DBPARENTID;
if (

numrows = 0
)
then
raise_application_error(
-20007,
'Cannot UPDATE LU_MAJOR because LU_PARENT does not exist.' );
end if;
end; Received on Thu Sep 29 2005 - 19:13:03 CEST