Re: Why does my relationship conflict with my trigger?

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 Sun Jan 28 2001 - 19:40:44 CST