Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Trigger Update Date
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_" Received on Wed Jan 01 2003 - 20:21:06 CST