Oracle Trigger - Column Level Auditing

A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.

This is simple example is based on oracle database table and demonstrate how to log the column data change within table. Create table employees to demonstrate.

CREATE TABLE EMPLOYEES 
  (    EMPLOYEE_ID 		NUMBER(6,0),
       NAME 			VARCHAR2(20),
       EMAIL 			VARCHAR2(25),
       PHONE_NUMBER 	VARCHAR2(20),
       LAST_UPDATED_BY  VARCHAR2(32),
       CONSTRAINT		EMPLOYEES_EMPLOYEE_ID_PK PRIMARY KEY (EMPLOYEE_ID)                  
   );    

Create table to log down the changes in employees table.

CREATE TABLE LOG
 (    LOG_ID 			NUMBER(12),
      TRANSACTION_ID	NUMBER(10),
      TRANSACTION_DATE	DATE,
      TABLE_NAME		VARCHAR2(32),
      TABLE_PK_VALUE	VARCHAR2(32),
      COLUMN_NAME		VARCHAR2(32),
      PREVIOUS_VALUE	VARCHAR2(64),
      CHANGED_VALUE		VARCHAR2(64),
      CHANGE_BY			VARCHAR2(32),
      CONSTRAINT		LOG_LOG_ID_PK PRIMARY KEY (LOG_ID)
 );          

Insert some records to the employees table

INSERT INTO employees VALUES (100, 	'Steven', 	'sking@something.com', 	'515.123.4567', 	NULL);
INSERT INTO employees VALUES (101, 	'Neena', 	'nkochhar@something.com', '515.123.4568',   NULL);

COMMIT;

Now, create trigger to track down the update on employees table and logs updated information to log table.

CREATE OR REPLACE TRIGGER TR_EMPLOYEE_UPDATE
BEFORE UPDATE ON EMPLOYEES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE

	v_transaction_id number(10);

BEGIN

	SELECT NVL(MAX(TRANSACTION_ID), 0) + 1 
      INTO v_transaction_id 
      FROM LOG;             
      
     /* -- for  EMPLOYEE_ID Column -- */
     IF (:OLD.EMPLOYEE_ID != :NEW.EMPLOYEE_ID) THEN

          INSERT INTO log
               (log_id, transaction_id, transaction_date, table_name,
                table_pk_value, column_name, previous_value, changed_value,
                change_by)
          VALUES
               ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM LOG),
                v_transaction_id, SYSDATE, 'EMPLOYEES',
                :NEW.EMPLOYEE_ID,
                'EMPLOYEE_ID', :OLD.EMPLOYEE_ID, :NEW.EMPLOYEE_ID,
                :NEW.last_updated_by);
     END IF;      
      
      
     /* -- for  NAME Column -- */
     IF (:OLD.NAME != :NEW.NAME) THEN

          INSERT INTO log
               (log_id, transaction_id, transaction_date, table_name,
                table_pk_value, column_name, previous_value, changed_value,
                change_by)
          VALUES
               ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM LOG),
                v_transaction_id, SYSDATE, 'EMPLOYEES',
                :NEW.EMPLOYEE_ID,
                'NAME', :OLD.NAME, :NEW.NAME,
                :NEW.last_updated_by);
     END IF;            
      

     /* -- for  EMAIL Column -- */
     IF (:OLD.EMAIL != :NEW.EMAIL) THEN

          INSERT INTO log
               (log_id, transaction_id, transaction_date, table_name,
                table_pk_value, column_name, previous_value, changed_value,
                change_by)
          VALUES
               ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM LOG),
                v_transaction_id, SYSDATE, 'EMPLOYEES',
                :NEW.EMPLOYEE_ID,
                'EMAIL', :OLD.EMAIL, :NEW.EMAIL,
                :NEW.last_updated_by);
     END IF;      
     
     
        
     /* -- for  PHONE_NUMBER Column -- */
     IF (:OLD.EMAIL != :NEW.EMAIL) THEN

          INSERT INTO log
               (log_id, transaction_id, transaction_date, table_name,
                table_pk_value, column_name, previous_value, changed_value,
                change_by)
          VALUES
               ((SELECT NVL(MAX(LOG_ID), 0) + 1 FROM LOG),
                v_transaction_id, SYSDATE, 'EMPLOYEES',
                :NEW.EMPLOYEE_ID,
                'PHONE_NUMBER', :OLD.PHONE_NUMBER, :NEW.PHONE_NUMBER,
                :NEW.last_updated_by);
     END IF;      
      

END TR_EMPLOYEE_UPDATE;

Let us update employee table

UPDATE employees 
   SET email 			= 'sking@test.com',
       phone_number 	= '123456789',
       last_updated_by 	= 'birijan'
 WHERE employee_id = 100;  

The result of the log file is as follows

SQL> select * from log;

    LOG_ID TRANSACTION_ID TRANSACTI TABLE_NAME TABLE_PK_VALUE COLUMN_NAME  PREVIOUS_VALUE      CHANGED_VALUE  CHANGE_BY
---------- -------------- --------- ---------- -------------- ------------ ------------------- -------------- ---------
         1              1 21-MAR-07 EMPLOYEES  100            EMAIL        sking@something.com sking@test.com birijan
         2              1 21-MAR-07 EMPLOYEES  100            PHONE_NUMBER 515.123.4567        123456789      birijan