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

<jeffchirco_at_gmail.com> wrote in message news:1128013983.291277.85850_at_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 Received on Thu Sep 29 2005 - 23:14:26 CEST