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

[Guest]

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;

[Mark C. Stock]

<jeffchirco@gmail.com> wrote in message
news:1128013983.291277.85850@g43g2000cwa.googlegroups.com...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;

1st, don't use triggers for enforcing RI -- use FK constraints

since oracle does not support CASCADE UPDATE, if absolutely have to update a
PK and cascade it to the FK (which really should never be necessary, since
by definition, PK values are non-updateable), then you can define your FK
constraint as deferable, which allows a transaction to violate the FK until
commit time. This would allow a normal update of the PK, followed by a
normal update of the FK

the cascade update of the FK might be doing in a row-level trigger (haven't
tried it recently) since the constraint is deferred, however, if it can't be
done, you would need to use PL/SQL tables (varrays) in a package and have
your after-row trigger write the old and new PK values to them, then use an
after-statement trigger to issue the updates

but try to design things so the PKs are not getting updated.

++ mcs

[Sybrand Bakker]

On 29 Sep 2005 10:13:03 -0700, "jeffchirco@gmail.com"
<jeffchirco@gmail.com> wrote:
I have a child parent releationship going. And I want to enforcereferentialintegrity with triggers. When I update the parent table I want it to godownand update the child. But if I update the child it needs to check tomake surethere is a matching parent.So I have a trigger after udate on the parent table that goes andupdates allthe childs.

Enforcing RI by means of triggers is unnecessary, as there are
constraints.
Also checking RI using constraints is about 8 times faster, as
checking RI using triggers.
If you check RI using constraints you don't need to select from the
parent table and you wouldn't run into this particular problem.

I think you need to revise your strategy.

--
Sybrand Bakker, Senior Oracle DBA