Logging Data Changes(DML) in Database

I have posted old post about Log DDL Changes in Your Schema, Today I decided to post new post about Log Data Changes in Database(DML Operations)
I decided to create generic solution that can be used in any database.

The Idea

I will create two tables only for storing every data changes in application.
I will create generic GENERATE_TRIGGER function (pass table name as parameter )to return script of trigger which I can use to log data changes in table.

I will store Data Logging in two separate tables(Master and detail Table) as below
Master table is for storing details about every transaction (DML) in database table
Detail table is for storing data changes in table data.

LOGGING_DATA_HDR Master Table

Contains main data about every DML applied to any table
1-LOG_ID sequence column that based on LOG_ID_SEQ sequence.
2-TABLE_NAME refers to table which DML applied on it.
2-PK_DATA contains primary key value of table.
    If primary key is composite key, it separated columns by "-" string.
3-ROW_ID refers to ROWID of table..
4-LOG_DATE refers to Time stamp execution of DML in table.
5-OPERATION_TYPE refers to DML type.
    INSERT ==> "I"
    DELete  ==> "D"
    UPDATE==> "U"
6-DB_USER refers to database user which executed the DML statement.
7-CLIENT_IP refers to IP of machine which from it DML statement is executed.
8-CLIENT_HOST refers to host name of machine which from it DML statement is executed.
9-CLIENT_OS_USER refers to operating system user of machine which from it DML statement is executed.
10-APP_USER refers to application user if I used application user, I get it from  GC$APP_USER variable in MAHMOUD_LOGGING package.

LOGGING_DATA_DTL Detail Table

1-LOG_ID is foreign key to LOGGING_DATA_HDR table.
2-COLUMN_NAME refers to column name in table.
3-OLD_VALUE refers to old value before execution of DML statement.
4-NEW_VALUE refers to new value after execution of DML Statement.

Scripts of Tables and Sequence

 CREATE TABLE LOGGING_DATA_HDR  
 (  
   LOG_ID      NUMBER,  
   TABLE_NAME    VARCHAR2 (30 CHAR) NOT NULL,  
   PK_DATA     VARCHAR2 (500 BYTE),  
   ROW_ID      ROWID NOT NULL,  
   LOG_DATE     DATE NOT NULL,  
   OPERATION_TYPE  VARCHAR2 (1 BYTE) NOT NULL,  
   DB_USER     VARCHAR2 (100 BYTE),  
   CLIENT_IP    VARCHAR2 (40 BYTE),  
   CLIENT_HOST   VARCHAR2 (100 BYTE),  
   CLIENT_OS_USER  VARCHAR2 (100 BYTE),  
   APP_USER     VARCHAR2 (50 BYTE)  
 );  
 ALTER TABLE LOGGING_DATA_HDR ADD (  
  CONSTRAINT LOGGING_DATA_HDR_PK  
  PRIMARY KEY  
  (LOG_ID)  
  );  
 CREATE TABLE LOGGING_DATA_DTL  
 (  
   LOG_ID    NUMBER,  
   COLUMN_NAME  VARCHAR2 (30 CHAR),  
   OLD_VALUE   VARCHAR2 (4000 BYTE),  
   NEW_VALUE   VARCHAR2 (4000 BYTE)  
 );  
 ALTER TABLE LOGGING_DATA_DTL ADD (  
  CONSTRAINT LOGGING_DATA_DTL_PK  
  PRIMARY KEY  
  (LOG_ID, COLUMN_NAME));  
 CREATE SEQUENCE LOG_ID_SEQ  
   START WITH 1  
   MAXVALUE 99999999999  
   MINVALUE 1  
   NOCYCLE  
   NOCACHE  
   NOORDER;  

 

MAHMOUD_LOGGING Package

I created MAHMOUD_LOGGING package to help me in this solution.
It contain below PSQL units
a-ADD_LOG procedure
   I use it to add new log to logging tables(LOGGING_DATA_HDR,LOGGING_DATA_DTL)
b-GENERATE_TRIGGER
   I use it to get trigger script for table which I want to create logging on it.
c-GET_COMPOSITE_KEY
   I use it to get primary key data values in table for current record used in DML operation.
   If primary key is composite, function separated columns by "-" string
d-SET_APP_USER
   If I use application that has its own users( not db users), I use this procedure to set current application user.
   You should use it into your application in logging time so you should call it like below
    I assume that application user is "Mahmoud A. Mahmoud"
    MAHMOUD_LOGGING.SET_APP_USER('Mahmoud A. Mahmoud');
e-GET_APP_USER
   I use it to get current logged application user.

MAHMOUD_LOGGING Package Script
MAHMOUD_LOGGING Package Specification 

 CREATE OR REPLACE PACKAGE MAHMOUD_LOGGING  
 AS  
   TYPE GT$LOGGING_DATA_DTL IS TABLE OF LOGGING_DATA_DTL%ROWTYPE;  
   GC$APP_USER  LOGGING_DATA_HDR.APP_USER%TYPE;  
   PROCEDURE ADD_LOG (IN_ARRAY      IN GT$LOGGING_DATA_DTL,  
            IN_TABLE_NAME     VARCHAR2,  
            IN_ROWID        ROWID,  
            IN_OPERATION_TYPE   VARCHAR2);  
   FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)  
    RETURN VARCHAR2;  
   FUNCTION GET_COMPOSITE_KEY (IN_TABLE    VARCHAR2,  
                 IN_ROWID    ROWID,  
                 IN_DELIMETER  VARCHAR2 DEFAULT '-')  
    RETURN VARCHAR2;  
   PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE);  
   FUNCTION GET_APP_USER  
    RETURN LOGGING_DATA_HDR.APP_USER%TYPE;  
 END MAHMOUD_LOGGING;  
 /  

MAHMOUD_LOGGING Package Body 

 CREATE OR REPLACE PACKAGE BODY MAHMOUD_LOGGING  
 AS  
   PROCEDURE ADD_LOG (IN_ARRAY      IN GT$LOGGING_DATA_DTL,  
            IN_TABLE_NAME     VARCHAR2,  
            IN_ROWID        ROWID,  
            IN_OPERATION_TYPE   VARCHAR2)  
   IS  
    LN$LOG_ID  LOGGING_DATA_HDR.LOG_ID%TYPE;  
   BEGIN  
    SELECT LOG_ID_SEQ.NEXTVAL INTO LN$LOG_ID FROM DUAL;  
    INSERT INTO LOGGING_DATA_HDR (LOG_ID,  
                   TABLE_NAME,  
                   PK_DATA,  
                   ROW_ID,  
                   LOG_DATE,  
                   OPERATION_TYPE,  
                   DB_USER,  
                   CLIENT_IP,  
                   CLIENT_HOST,  
                   CLIENT_OS_USER,  
                   APP_USER)  
       VALUES (LN$LOG_ID,  
           IN_TABLE_NAME,  
           GET_COMPOSITE_KEY (IN_TABLE_NAME, IN_ROWID),  
           IN_ROWID,  
           SYSDATE,  
           IN_OPERATION_TYPE,  
           SYS_CONTEXT ('USERENV', 'CURRENT_USER'),  
           SYS_CONTEXT ('USERENV', 'ip_address'),  
           SYS_CONTEXT ('USERENV', 'host'),  
           SYS_CONTEXT ('USERENV', 'os_user'),  
           GET_APP_USER);  
    IF IN_ARRAY IS NOT NULL AND IN_ARRAY.COUNT > 0  
    THEN  
      FOR INDX IN IN_ARRAY.FIRST .. IN_ARRAY.LAST  
      LOOP  
       IF IN_ARRAY (INDX).COLUMN_NAME IS NOT NULL  
         AND (IN_ARRAY (INDX).OLD_VALUE IS NOT NULL  
           OR IN_ARRAY (INDX).NEW_VALUE IS NOT NULL)  
       THEN  
         INSERT INTO LOGGING_DATA_DTL (LOG_ID,  
                        COLUMN_NAME,  
                        OLD_VALUE,  
                        NEW_VALUE)  
           VALUES (LN$LOG_ID,  
               IN_ARRAY (INDX).COLUMN_NAME,  
               IN_ARRAY (INDX).OLD_VALUE,  
               IN_ARRAY (INDX).NEW_VALUE);  
       END IF;  
      END LOOP;  
    END IF;  
   END ADD_LOG;  
   FUNCTION GENERATE_TRIGGER (IN_TABLE_NAME VARCHAR2)  
    RETURN VARCHAR2  
   IS  
    LC$TRIGGER_STMT  VARCHAR2 (4000);  
    CURSOR LCUR$COLUMNS  
    IS  
       SELECT COLUMN_NAME  
        FROM USER_TAB_COLS  
       WHERE TABLE_NAME = IN_TABLE_NAME  
      ORDER BY COLUMN_ID;  
   BEGIN  
    LC$TRIGGER_STMT :=  
       'CREATE OR REPLACE TRIGGER '  
      || SUBSTR (IN_TABLE_NAME, 1, 23)  
      || '_LOGTRG '  
      || CHR (10)  
      || 'AFTER INSERT OR UPDATE OR DELETE'  
      || CHR (10)  
      || 'ON '  
      || IN_TABLE_NAME  
      || ' FOR EACH ROW '  
      || CHR (10)  
      || 'DECLARE '  
      || CHR (10)  
      || 'LT$LOGGING_DATA_DTL  MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;'  
      || CHR (10)  
      || 'LC$OPERATION     VARCHAR2 (1);'  
      || CHR (10)  
      || 'PROCEDURE ADD_ELEMENT ('  
      || CHR (10)  
      || 'IN_OPERATION   VARCHAR2,'  
      || CHR (10)  
      || 'IN_COLUMN_NAME  LOGGING_DATA_DTL.COLUMN_NAME%TYPE,'  
      || CHR (10)  
      || 'IN_OLD_VALUE   LOGGING_DATA_DTL.OLD_VALUE%TYPE,'  
      || CHR (10)  
      || 'IN_NEW_VALUE   LOGGING_DATA_DTL.NEW_VALUE%TYPE)'  
      || CHR (10)  
      || 'IS'  
      || CHR (10)  
      || 'LR$LOGGING_DATA_DTL  LOGGING_DATA_DTL%ROWTYPE;'  
      || CHR (10)  
      || 'BEGIN'  
      || CHR (10)  
      || ' IF NOT (IN_OPERATION = ''U'' AND IN_NEW_VALUE = IN_OLD_VALUE)'  
      || CHR (10)  
      || 'THEN'  
      || CHR (10)  
      || 'LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;'  
      || CHR (10)  
      || 'LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;'  
      || CHR (10)  
      || 'LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;'  
      || CHR (10)  
      || 'LT$LOGGING_DATA_DTL.EXTEND;'  
      || CHR (10)  
      || 'LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;'  
      || CHR (10)  
      || 'END IF;'  
      || CHR (10)  
      || 'END ADD_ELEMENT;'  
      || CHR (10)  
      || 'BEGIN'  
      || CHR (10)  
      || 'LT$LOGGING_DATA_DTL := MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();'  
      || CHR (10)  
      || 'LC$OPERATION :='  
      || CHR (10)  
      || 'CASE WHEN INSERTING THEN ''I'' WHEN UPDATING THEN ''U'' ELSE ''D'' END;'  
      || CHR (10);  
    FOR LREC$COLUMNS IN LCUR$COLUMNS  
    LOOP  
      LC$TRIGGER_STMT :=  
         LC$TRIGGER_STMT  
       || ' ADD_ELEMENT (LC$OPERATION,'''  
       || LREC$COLUMNS.COLUMN_NAME  
       || ''',:OLD.'  
       || LREC$COLUMNS.COLUMN_NAME  
       || ',:NEW.'  
       || LREC$COLUMNS.COLUMN_NAME  
       || ');'  
       || CHR (10);  
    END LOOP;  
    LC$TRIGGER_STMT :=  
       LC$TRIGGER_STMT  
      || ' MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'''  
      || IN_TABLE_NAME  
      || ''',:NEW.ROWID,LC$OPERATION);'  
      || CHR (10)  
      || 'END '  
      || SUBSTR (IN_TABLE_NAME, 1, 23)  
      || '_LOGTRG ;';  
    RETURN LC$TRIGGER_STMT;  
   END GENERATE_TRIGGER;  
   FUNCTION GET_COMPOSITE_KEY (IN_TABLE    VARCHAR2,  
                 IN_ROWID    ROWID,  
                 IN_DELIMETER  VARCHAR2 DEFAULT '-')  
    RETURN VARCHAR2  
   IS  
    PRAGMA AUTONOMOUS_TRANSACTION;  
    LC$COLUMNS  VARCHAR2 (512) := '';  
    LC$KEY    VARCHAR2 (512);  
    CURSOR LCUR$COLUMNS (  
      IN_TABLE_NAME VARCHAR2)  
    IS  
       SELECT CON_C.COLUMN_NAME  
        FROM USER_CONS_COLUMNS CON_C, USER_CONSTRAINTS CON  
       WHERE   CON.CONSTRAINT_NAME = CON_C.CONSTRAINT_NAME  
          AND CON.CONSTRAINT_TYPE = 'P'  
          AND CON.TABLE_NAME = IN_TABLE_NAME  
      ORDER BY POSITION;  
   BEGIN  
    FOR LREC$COLUMNS IN LCUR$COLUMNS (IN_TABLE)  
    LOOP  
      LC$COLUMNS :=  
         LC$COLUMNS  
       || LREC$COLUMNS.COLUMN_NAME  
       || '||'''  
       || IN_DELIMETER  
       || '''||';  
    END LOOP;  
    LC$COLUMNS := RTRIM (LC$COLUMNS, '||''' || IN_DELIMETER || '''||');  
    EXECUTE IMMEDIATE  
       'SELECT '  
      || LC$COLUMNS  
      || ' FROM '  
      || IN_TABLE  
      || ' WHERE ROWID='''  
      || IN_ROWID  
      || ''''  
      INTO LC$KEY;  
    RETURN LC$KEY;  
   END GET_COMPOSITE_KEY;  
   PROCEDURE SET_APP_USER (IN_APP_USER LOGGING_DATA_HDR.APP_USER%TYPE)  
   IS  
   BEGIN  
    GC$APP_USER := IN_APP_USER;  
   END SET_APP_USER;  
   FUNCTION GET_APP_USER  
    RETURN LOGGING_DATA_HDR.APP_USER%TYPE  
   IS  
   BEGIN  
    RETURN GC$APP_USER;  
   END GET_APP_USER;  
 END MAHMOUD_LOGGING;  
 /  

Demo on MAHMOUD_LOGGING Package

1-  I will use EMP table in SCOTT schema to implement logging on it
      To generate logging trigger in table EMP use below script

 BEGIN  
   DBMS_OUTPUT.PUT_LINE (MAHMOUD_LOGGING.GENERATE_TRIGGER ('EMP'));  
 END;  

It will print trigger script in DBMS Output, Let's copy it and run it in SQL plus

 CREATE OR REPLACE TRIGGER EMP_LOGTRG   
 AFTER INSERT OR UPDATE OR DELETE  
 ON EMP FOR EACH ROW   
 DECLARE   
 LT$LOGGING_DATA_DTL  MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL;  
 LC$OPERATION     VARCHAR2 (1);  
 PROCEDURE ADD_ELEMENT (  
 IN_OPERATION   VARCHAR2,  
 IN_COLUMN_NAME  LOGGING_DATA_DTL.COLUMN_NAME%TYPE,  
 IN_OLD_VALUE   LOGGING_DATA_DTL.OLD_VALUE%TYPE,  
 IN_NEW_VALUE   LOGGING_DATA_DTL.NEW_VALUE%TYPE)  
 IS  
 LR$LOGGING_DATA_DTL  LOGGING_DATA_DTL%ROWTYPE;  
 BEGIN  
  IF NOT (IN_OPERATION = 'U' AND IN_NEW_VALUE = IN_OLD_VALUE)  
 THEN  
 LR$LOGGING_DATA_DTL.COLUMN_NAME := IN_COLUMN_NAME;  
 LR$LOGGING_DATA_DTL.OLD_VALUE :=IN_OLD_VALUE;  
 LR$LOGGING_DATA_DTL.NEW_VALUE := IN_NEW_VALUE;  
 LT$LOGGING_DATA_DTL.EXTEND;  
 LT$LOGGING_DATA_DTL (LT$LOGGING_DATA_DTL.LAST) := LR$LOGGING_DATA_DTL;  
 END IF;  
 END ADD_ELEMENT;  
 BEGIN  
 LT$LOGGING_DATA_DTL := MAHMOUD_LOGGING.GT$LOGGING_DATA_DTL ();  
 LC$OPERATION :=  
 CASE WHEN INSERTING THEN 'I' WHEN UPDATING THEN 'U' ELSE 'D' END;  
  ADD_ELEMENT (LC$OPERATION,'EMPNO',:OLD.EMPNO,:NEW.EMPNO);  
  ADD_ELEMENT (LC$OPERATION,'ENAME',:OLD.ENAME,:NEW.ENAME);  
  ADD_ELEMENT (LC$OPERATION,'JOB',:OLD.JOB,:NEW.JOB);  
  ADD_ELEMENT (LC$OPERATION,'MGR',:OLD.MGR,:NEW.MGR);  
  ADD_ELEMENT (LC$OPERATION,'HIREDATE',:OLD.HIREDATE,:NEW.HIREDATE);  
  ADD_ELEMENT (LC$OPERATION,'SAL',:OLD.SAL,:NEW.SAL);  
  ADD_ELEMENT (LC$OPERATION,'COMM',:OLD.COMM,:NEW.COMM);  
  ADD_ELEMENT (LC$OPERATION,'DEPTNO',:OLD.DEPTNO,:NEW.DEPTNO);  
  MAHMOUD_LOGGING.ADD_LOG (LT$LOGGING_DATA_DTL,'EMP',:NEW.ROWID,LC$OPERATION);  
 END EMP_LOGTRG ;  

2- I will do Insert, Update and Delete operations in EMP table

 EXEC MAHMOUD_LOGGING.SET_APP_USER('Mahmoud A. Mahmoud');  
 INSERT INTO emp (EMPNO,  
          ENAME,  
          JOB,  
          DEPTNO)  
    VALUES (150,  
        'Mahmoud',  
        'Dev',  
        10);  
 UPDATE emp  
   SET job = 'S. Dev', deptno = 20  
  WHERE empno = 150;  
 DELETE FROM emp  
    WHERE empno = 150;  
 COMMIT;  

3-Now see data in logging tables
LOGGING_DATA_HDR Table

 
LOGGING_DATA_DTL Table

Conclusion

I created this dynamic solution for logging to be compatible at any application and anyone can use it in his application.
If you have a massive data, so you should partition logging table of your database supports partitioning.
You should do logging for tables that have sensitive data or track data changes.
Don't use logging for unnecessary data in order to save storage in your data files.

Thanks
Mahmoud A. El-Sayed