Oracle Trigger Updating a field on an Insert or an Update

Question

Hi all,

For some reason i'm running a blank on how to go about doing something like this.

I have a table that looks like this

UserID   |  Name   |  DateAdded   |   LastUpated
--------------------------------------------------
1        | James Q | 1/1/2009     |

If i insert or update record the lastupdated field should be updated the sysdate. How would i go about doing something like this?

Thanks

Answer 1

CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE INSERT OR UPDATE
ON your_table
FOR EACH ROW
DECLARE
BEGIN
   :new.LastUpdated := sysdate;
END;

Try that. I did not have a oracle server at hand, but I hope I got the syntax right.

Hope it helps,

/Klaus

Answer 2

create or replace trigger mytable_bi before insert on mytable for each row begin

:NEW.lastupdated := sysdate();

end;

create or replace trigger mytable_bu before update on mytable for each row begin

:NEW.lastupdated := sysdate();

end;