Re: What is Wrong with this Trigger?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 09 Feb 1999 22:50:02 GMT
Message-ID: <36c0ba97.17806774@inet16.us.oracle.com>

On Tue, 9 Feb 1999 15:41:47 -0600, "John Jennings" <jjennings_at_hertz.com> wrote:

>I have a trigger that is supposed to stamp a date. Here is a layout of my
>table.
>
>Problem_Code varchar(15)
>Date_Resolved Date
>
>Trigger Text:
>
>CREATE OR REPLACE TRIGGER "EXAV"."UPD_PCODE_RESOLVED"
>BEFORE INSERT OR UPDATE OF "PROBLEM_CODE" ON "EXAV"."PROBLEMS"
>REFERENCING OLD AS OLD NEW AS NEW
>FOR EACH ROW
>WHEN (NEW.PROBLEM_CODE='RESOLVED')
>BEGIN
>UPDATE PROBLEMS SET DATE_RESOLVED=SYSDATE;
>END;
>
>Here is the error message when 'Problem_Code' = 'RESOLVED':
>
>"Table Problems is mutating, trigger/function may not see it. ORA-06512: at
>"EXAV.UPD_PCODE_RESOLVED", line 2
>ORA-04088: error during execution of trigger 'EXAV.UPD_PCODE_RESOLVED'
>(#4091).
>

You can't issue an update on the table that the trigger is attached to. That is what is causing the mutating trigger. Instead of update table, all you need to do is this...

  :new.date_resolved := sysdate;

which says to set the new value of the date_resolved column to sysdate.

hope this helps.

chris.

>Any assistance is appreciated.
>
>JJ
>
>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Feb 09 1999 - 16:50:02 CST