jump to navigation

Automatically update timestamps on Oracle January 26, 2007

Posted by maxmil in : Oracle , trackback

I often use the Mysql feature that automátically updates a timestamp column every time that the row is modified or inserted. This is very useful for keeping track of the last edit of an object. In Mysql i use this syntax when defining a column:
`fieldName` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP
I have been looking for something similar on Oracle 9i. I’ve found that the triggers that Oracle offers are very potent and can easily achieve this. Heres how you do it.

CREATE OR REPLACE TRIGGER <SCHEMA>.<TRIGGERNAME>
BEFORE INSERT OR UPDATE
ON <SCHEMA>.<TABLENAME>
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
:NEW.<DATE FIELD> := SYSDATE;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
/

Comments»

no comments yet - be the first?