Track table data changes (inserts, updates, deletes)

Summary 

This article describes a classic method of capturing table changes (insert, updates, deletes). Table changes are stored in another table along with user information who did the change. 

The concept is simple, we will create a trigger that capture the dml statements on the table. 

To make the example we will use a user that owns the history of changes and source code 

user: MONITOR 
table to capture changes: OBJECTS 
history of changes: OBJECTS_H 
trigger: OBJECTS_DML on OBJECTS 

Connect as sys and create user MONITOR

CREATE USER monitor IDENTIFIED BY monitor;
GRANT CONNECT, RESOURCE TO monitor;
CREATE TABLESPACE monitor_data DATAFILE '/ora2/ermis/oradata/ORA/monitor_data_01.dbf' SIZE 30M;
ALTER USER MONITOR DEFAULT TABLESPACE MONITOR_DATA TEMPORARY TABLESPACE TEMP;

GRANT SELECT ON V_$SESSION TO MONITOR;

Create table OBJECTS and the table for history changes OBJECTS_H

CREATE TABLE MONITOR.OBJECTS TABLESPACE MONITOR_DATA 
AS SELECT * FROM DBA_OBJECTS;

CREATE TABLE MONITOR.OBJECTS_H TABLESPACE MONITOR_DATA 
AS SELECT * FROM MONITOR.OBJECTS 
WHERE 1=2;

ALTER TABLE MONITOR.OBJECTS_H ADD (USERNAME   VARCHAR2(30));
ALTER TABLE MONITOR.OBJECTS_H ADD (OSUSER   VARCHAR2(30));
ALTER TABLE MONITOR.OBJECTS_H ADD (MACHINE   VARCHAR2(64));
ALTER TABLE MONITOR.OBJECTS_H ADD (PROGRAM   VARCHAR2(48));
ALTER TABLE MONITOR.OBJECTS_H ADD (LOGON_TIME   DATE);
ALTER TABLE MONITOR.OBJECTS_H ADD (CHANGE_TIME   DATE);
ALTER TABLE MONITOR.OBJECTS_H ADD (CHANGE_TYPE   VARCHAR2(10));

Create the trigger MONITOR.OBJECTS_DML

CREATE OR REPLACE TRIGGER MONITOR.OBJECTS_DML BEFORE INSERT OR DELETE OR UPDATE
ON monitor.OBJECTS FOR EACH ROW
DECLARE

username1 VARCHAR2(30);
osuser1 VARCHAR2(30);
machine1 VARCHAR2(64);
program1 VARCHAR2(48);
logon_time1 DATE;

  BEGIN

  SELECT USERNAME,OSUSER ,MACHINE , PROGRAM , LOGON_TIME
  INTO USERNAME1,OSUSER1 ,MACHINE1 , PROGRAM1 , LOGON_TIME1
  FROM v$session
  WHERE audsid =( SELECT USERENV('SESSIONID') FROM DUAL);

    IF INSERTING THEN
  INSERT INTO monitor.OBJECTS_H
(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
USERNAME,OSUSER ,MACHINE , PROGRAM , LOGON_TIME, CHANGE_TIME, CHANGE_TYPE)
  VALUES (
  :NEW.OWNER, :NEW.OBJECT_NAME, :NEW.SUBOBJECT_NAME, :NEW.OBJECT_ID, :NEW.DATA_OBJECT_ID,
:NEW.OBJECT_TYPE, :NEW.CREATED, :NEW.LAST_DDL_TIME, :NEW.TIMESTAMP, :NEW.STATUS, 
:NEW.TEMPORARY, :NEW.GENERATED, :NEW.SECONDARY,
     USERNAME1,OSUSER1 ,MACHINE1 , PROGRAM1 , LOGON_TIME1, SYSDATE, 'INS' ) ;

    ELSIF DELETING THEN
INSERT INTO monitor.OBJECTS_H
(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
USERNAME,OSUSER ,MACHINE , PROGRAM , LOGON_TIME, CHANGE_TIME, CHANGE_TYPE)
  VALUES (
  :OLD.OWNER, :OLD.OBJECT_NAME, :OLD.SUBOBJECT_NAME, :OLD.OBJECT_ID, :OLD.DATA_OBJECT_ID,
:OLD.OBJECT_TYPE, :OLD.CREATED, :OLD.LAST_DDL_TIME, :OLD.TIMESTAMP, :OLD.STATUS, 
:OLD.TEMPORARY, :OLD.GENERATED, :OLD.SECONDARY,
     USERNAME1,OSUSER1 ,MACHINE1 , PROGRAM1 , LOGON_TIME1, SYSDATE, 'DEL' ) ;

    ELSIF UPDATING  THEN
  INSERT INTO monitor.OBJECTS_H
(OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY,
USERNAME,OSUSER ,MACHINE , PROGRAM , LOGON_TIME, CHANGE_TIME, CHANGE_TYPE)
  VALUES (
  :OLD.OWNER, :OLD.OBJECT_NAME, :OLD.SUBOBJECT_NAME, :OLD.OBJECT_ID, :OLD.DATA_OBJECT_ID,
:OLD.OBJECT_TYPE, :OLD.CREATED, :OLD.LAST_DDL_TIME, :OLD.TIMESTAMP, :OLD.STATUS, 
:OLD.TEMPORARY, :OLD.GENERATED, :OLD.SECONDARY,
     USERNAME1,OSUSER1 ,MACHINE1 , PROGRAM1 , LOGON_TIME1, SYSDATE, 'UPD' ) ;
    END IF;

  END;
/

Test it, do some updates and deletes and check if captured

UPDATE MONITOR.OBJECTS SET OBJECT_TYPE = 'PROC' WHERE OBJECT_ID = 89
COMMIT;
DELETE FROM MONITOR.OBJECTS WHERE OBJECT_ID = 89;
COMMIT;

SELECT * FROM monitor.OBJECTS_H;