Trigger for Audit Trail in Oracle
For the Audit trail of a transactional or master data table, we can use the following format to trace the differences. First, create a table for storing audit data named as < SCHEMA_NAME>.< TABLE_NAME>_AUDIT CREATE TABLE < SCHEMA_NAME>.< TABLE_NAME>_AUDIT ( COLUMN_NAME TYPE, ACTION CHAR(1), ACTION_DATE DATE, ACTION_USER_ID NUMBER(18), CLIENT_DEVICE VARCHAR2(100), CLIENT_OS_USER VARCHAR2(100) ); Then, create a trigger named as < TABLE_NAME>_TRIGGER CREATE OR REPLACE TRIGGER < TABLE_NAME>_TRIGGER AFTER INSERT OR UPDATE OR DELETE ON < TABLE_NAME> FOR EACH ROW DECLARE V_USER_ID NUMBER(9); V_CLIENT_DEVICE VARCHAR2(100); V_CLIENT_OS_USER VARCHAR2(100); BEGIN SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER' ), SYS_CONTEXT('USERENV', 'HOST' ),