Re: Trigger Update Date

AcCeSsDeNiEd wrote:

> Oracle noob here.
>
> I'm trying to create a trigger that will update the "Mod_Date" field when a row of data is updated.
>
> So far I've come up with this:
>
> CREATE or replace TRIGGER mod_ded_trig
> BEFORE UPDATE OF NAME1 ON ADMIN.TEST
> BEGIN
> update ADMIN.TEST set Mod_DED=sysdate;
> END;
>
> Running the query:
>
> update admin.test set Name1='MyName' where Name1='OtherName';
>
> This ends up updating all the rows of data in the 'admin.test' table with the new date regardless or
> not the row is updated (via NAME1).
>
> I just wanna update the mod_date of only the rows that are affected.
>
> So I replaced the trigger with this:
>
> CREATE or replace TRIGGER mod_ded_trig
> BEFORE UPDATE OF NAME ON ADMIN.TEST
> for each row
> BEGIN
> update ADMIN.TEST set Mod_DED=sysdate;
> END;
>
> This produces an error when I attempt to update a row:
>
> "ORA-04091: table ADMIN.TEST is mutating, trigger/function may not see it
> ORA-06512: at "MOD_DED_TRIG", line 2
> ORA-04088: error during execution of trigger 'MOD_DED_TRIG'
>
> Any ideas / sample code?
> I've searched google and my books and came up with nothing.
>
> Thanks.
>
> To e-mail me, remove "rm_"

In a trigger updating a table you NEVER write an update statement unless it is to update a different table. All you need is:

BEGIN
   :NEW.test := SYSDATE;
END;
/

Go to http://tahiti.oracle.com and search under :NEW and :OLD (with the colon)

Daniel Morgan Received on Thu Jan 02 2003 - 13:01:16 CST