Sample Audit Scripts
The following sample scripts provide an example of audit triggers, sequence generators, and sequence triggers. Modify these scripts as needed for your system.
Sample Audit Column Scripts
The following sample scripts provide an example of how to generate audit triggers on specific columns.
To generate a trigger on the audit column LST_UPD_DT on every table in the work or staging area data schema:
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE UPDATE OR INSERT ON
TABLE_NAME FOR EACH ROW
BEGIN :new.LST_UPD_DT := SYSDATE ; END;
To generate a trigger on the audit column S2P_TRN_DT in every table in the release or production data schema:
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE UPDATE OR INSERT ON
TABLE_NAME FOR EACH ROW BEGIN :new.S2P_TRN_DT := SYSDATE ; END;
Sample Sequence Generation and Trigger Sample Scripts
The following sample scripts provide an example of how to generate sequence DDL definition and sequence triggers.
To generate a sample sequence DDL definition:
CREATE SEQUENCE SEQUENCE_NAME START WITH 1 MAXVALUE 999999999 NOCACHE;
To generate a sample sequence trigger:
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
BEGIN select SEQUENCE_NAME.nextval into :new.PK_ID from dual ;
END;
To generate a sample sequence trigger where data can be created using an iHook
CREATE OR REPLACE TRIGGER TRIGGER_NAME BEFORE INSERT ON TABLE_NAME
FOR EACH ROW
BEGIN IF (:NEW.PK_ID IS NULL OR :NEW.PK_ID = -1) THEN
select SEQUENCE_NAME.nextval into :NEW.PK_ID from dual ; END IF;
END;
Sample Function-Based Unique Constraint Scripts
You can create function based case-insensitive unique constraints. The following sample DDL is for a case-insensitive unique constraint that is generated from a modeling tool.
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME(upper(COLUMN_NAME,..))