Ed The Dev .com

Nearly every database record insert should automatically populate two required fields: ID and ADDED_DATE. Every table should have these fields, and it would be nice if the database automatically filled them in for you each time you add a record.

This script creates a trigger that populates those two fields for you each time you insert a new ‘RECORD’.

CREATE OR REPLACE TRIGGER "DatabaseName"."RECORDADDED" 
before insert on "RECORDTABLE"

for each row begin

if inserting then

select CURRENTDATE into :NEW."ADDEDDATE" from dual;
if :NEW."RECORDID" is null then

select RecordIdSeq.nextval into :NEW."RECORDID" from dual;
end if;

end if; 
end;


/
ALTER TRIGGER "DatabaseName"."RECORD_ADDED" ENABLE;

CREATE SEQUENCE  "DatabaseName"."RecordIdSeq"  MINVALUE 1 MAXVALUE
 999999999999999999999999999 INCREMENT BY 1 START WITH 41 
CACHE 20 NOORDER  NOCYCLE ;

If you use this code, be sure to modify the DatabaseName to match yours and replace RECORD with a sensible table name.