Re: Trigger Update Date

Ok I got it.

Just needed more caffeine :)

For the benefit of google searches, here's the solution:

create or replace trigger mod_ded_trig
before UPDATE OF NAME1 ON ADMIN.TEST
for each row
begin
:new.Mod_DED := SYSDATE;
end;

On Thu, 02 Jan 2003 10:21:06 +0800, AcCeSsDeNiEd <dillon_at_rm_accessdenied.darktech.org> 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_"

To e-mail me, remove "rm_" Received on Wed Jan 01 2003 - 20:58:06 CST