Andrew Fraser DBA

Oracle date changed user changed columns and batch performance

Most core application tables benefit from columns like these:

ALTER TABLE mytable ADD (
     date_created DATE DEFAULT SYSDATE
   , user_created_by VARCHAR2(30) DEFAULT USER
   , date_changed DATE
   , user_changed_by VARCHAR2(30)
) ;

The default values deal with inserts, for updates a trigger is needed:

CREATE OR REPLACE TRIGGER tu_mytable
BEFORE UPDATE ON mytable
FOR EACH ROW
BEGIN
   :NEW.date_changed := SYSDATE ;
   :NEW.user_changed_by := USER ;
END ;
/

These column can be useful for improving batch processing performance, especially with an index on NVL ( date_changed , date_created ), by allowing selects to be limited to the small set of records changed since the last run.

If you need to record delete statements (instead of the preferable logical deletes that update a status column to ‘deleted’), then a full history table is needed along with a delete trigger.