Defining the audit triggers

Changes to the application tables should be recorded in real time, in an automated manner. Oracle database triggers will be used to accomplish this.

Getting ready

Before creating the triggers to track the changes, first ensure that you have created the application table.

How to do it..

The triggers will track any changes to the data in the APP_CUSTOMER table and transfer the information to the journal table:

1. 1. Delete Trigger: This trigger will track all deletes and place the deleted record into the journal table:

   CREATE OR REPLACE TRIGGER "TRI_APP_CUSTOMER_DEL"
   BEFORE DELETE
   ON APP_CUSTOMER REFERENCING NEW AS New OLD AS Old
   FOR EACH ROW
   BEGIN
   INSERT INTO APP_CUSTOMER_JRNL (APPROVED_BY, APPROVED_DATE, JRNL_ACTION, JRNL_DATE, JRNL_BY, CUST_SEQ, CUST_NUM, CUST_NAME, CUST_ADDRESS1, CUST_ADDRESS2 , CUST_CITY, CUST_PCODE_ZIP, CUST_STATE, CUST_COUNTRY, REGION, CREATE_DATE, CREATE_BY, UPDATE_DATE, UPDATE_BY )
   VALUES (:old.APPROVED_BY, :old.APPROVED_DATE, 'Delete', SYSDATE...