Re: Why does my relationship conflict with my trigger?

I think that you'll find that this is because you are trying to insert into a child table, whilst the parent table is being updated. Oracle doesn't like this, because it doesn't know whether the foreign key that the child is relying on will still exist after this updated completes - hence the mutating message.

Usual solutions are to perform the audit in another session using pipes or the new-fangled O8 autonomous transaction.

Thomas Kyte (Oracle) has some excellent information at his site http://govt.oracle.com/~tkyte/.

Andy

David Bradley <davidbradley_at_home.com> wrote in message news:wS3d6.128619$ge4.46555050_at_news2.rdc2.tx.home.com...
> The code has been changed to PERSON_ID. The error below is the result of
 an
> update.
>
>
> SQLWKS> UPDATE STI_PERSONNEL SET FNAME = 'TEST' WHERE ID = 1;
>
> ORA-04091: table TSS.STI_PERSONNEL is mutating, trigger/function may not
 see
> it
> ORA-06512: at "TSS.UT_PERSONNEL", line 3
> ORA-04088: error during execution of trigger 'TSS.UT_PERSONNEL'
>
>
> Wendy Smiley <wendy01_at_charter.net> wrote in message
> news:t793qnb4a7e367_at_corp.supernews.com...
> > Your insert statement references PERSONNEL_ID, but your constraint is on
> > PERSON_ID. Is one of these a typo?
> > Are you getting a error when the trigger fires, or does it just not
 appear
> > to be firing? What error do you get, if any?
> >
> > Mike.
> >
> > "David Bradley" <davidbradley_at_home.com> wrote in message
> > news:PhYc6.127331$ge4.45673433_at_news2.rdc2.tx.home.com...
> > > I cannot understand why the following trigger worked fine until I
 added
 the
> > > relationship below. I have tried changing it to an AFTER UPDATE
 trigger
 and
> > > that does not seem to work either. Could someone let me know what
 would
> > > cause this and
> > > what my solution should be.
> > >
> > > Thanks,
> > >
> > > David
> > >
> > >
> > >
> > > Trigger
> > > ---------------------------------------------------------
> > > CREATE OR REPLACE TRIGGER "TSS".UT_PERSONNEL
> > > BEFORE UPDATE ON "TSS"."STI_PERSONNEL"
> > > REFERENCING OLD AS OLD NEW AS NEW
> > > FOR EACH ROW
> > > BEGIN
> > >
> > > /* Handle the first name change. */
> > > IF :NEW.FNAME != :OLD.FNAME THEN
> > > INSERT INTO TSS.STI_PERSONNEL_HISTORY
> > > (ID,PERSONNEL_ID,CHANGE_DATE,OLD_VALUE,NEW_VALUE,DESCRIPTION)
> > > VALUES
> > >
> > >
> >
>

 TSS.SEQ_PERSONNEL_HISTORY_ID.NEXTVAL,:OLD.ID,SYSDATE,:OLD.FNAME,:NEW.FNAME,'
> > > FIRST NAME CHANGE');
> > > END IF;
> > >
> > > END;
> > >
> > >
> > >
> > > Relationship
> > > ---------------------------------------------------------
> > > ALTER TABLE TSS.STI_PERSONNEL_HISTORY
> > > DROP CONSTRAINT STI_PERS_HISTORY_PERSID;
> > > ALTER TABLE TSS.STI_PERSONNEL_HISTORY
> > > ADD( CONSTRAINT STI_PERS_HISTORY_PERSID
> > > FOREIGN KEY( PERSON_ID )
> > > REFERENCES TSS.STI_PERSONNEL(ID)
> > > ON DELETE CASCADE );
> > >
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Mon Jan 29 2001 - 03:06:06 CST