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"."RECORD_ADDED" 
before insert on "RECORD_TABLE"

for each row begin

if inserting then

select CURRENT_DATE into :NEW."ADDED_DATE" from dual;
if :NEW."RECORD_ID" is null then

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

end if; 
end;


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

And this script creates the Sequence required for the trigger above to work.

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.