Logging changes to row content, part 1

Well, you can create a table called DATA_CHANGE_AUDIT with the following structure to store data changes in all...

tables in the database:

CREATE TABLE DATA_CHANGE_AUDIT
(TABLE_NAME         VARCHAR2(30),
PRIMARY_KEY_VALUE  VARCHAR2(100),
COLUMN_NAME        VARCHAR2(30),
ACTION_DATE        DATE,
OLD_VALUE          VARCHAR2(4000),
NEW_VALUE          VARCHAR2(4000),
USER_NAME          VARCHAR2(30))

Now, you can create some functions and procedures to dynamically create all the log triggers for all tables in your schema. Make sure to set serveroutput to 'on' when executing the procedures.

CREATE OR REPLACE FUNCTION GENERATE_TRIG_NAME(p_Table_name IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
    RETURN SUBSTR('TRIG_'|| p_Table_name,1, 30);  

-- You may want to be careful as to ensure that this function generates unique name.
  
END GENERATE_TRIG_NAME;
/

CREATE OR REPLACE PROCEDURE CREATE_AUDIT_TRIG_DYNAMIC(p_Table_Name IN VARCHAR2) IS

   CURSOR C1 (p_Table IN VARCHAR2) IS
       SELECT t1.COLUMN_NAME, t3.DATA_TYPE, t1.POSITION, T4.LAST_POSITION
         FROM
          USER_CONS_COLUMNS t1,
          USER_CONSTRAINTS t2,
          USER_TAB_COLUMNS t3,
         (SELECT t6.TABLE_NAME, MAX(t5.POSITION) LAST_POSITION
         FROM USER_CONS_COLUMNS t5,
              USER_CONSTRAINTS t6
        WHERE
              t6.TABLE_NAME   = p_Table
          AND t5.CONSTRAINT_NAME  = t6.CONSTRAINT_NAME
          AND t6.CONSTRAINT_TYPE  = 'P'
          GROUP BY t6.TABLE_NAME ) t4
     WHERE
         t2.TABLE_NAME       = p_Table
     AND t2.TABLE_NAME       = t4.TABLE_NAME
     AND t1.CONSTRAINT_NAME  = t2.CONSTRAINT_NAME
     AND t2.CONSTRAINT_TYPE  = 'P'
     AND t1.TABLE_NAME       = t3.TABLE_NAME
     AND t1.COLUMN_NAME      = t3.COLUMN_NAME
     ORDER BY t1.POSITION;
   CURSOR c2(p_Table IN VARCHAR2) IS
     SELECT COLUMN_NAME, COLUMN_ID
       FROM
         USER_TAB_COLUMNS
       WHERE
          TABLE_NAME   = p_Table
       ORDER BY COLUMN_ID;

-- In Cursor C2 If you have columns like Modified by, or created by and or 
creation date you may want to exclude those columns by specifying AND 
COLUMN_NAME NOT IN 'MODIFIED_BY','DATE_MODIFIED','CREATED_BY','DATE_CREATED').

-- You must have other audit triggers on the table for before insert 
 or before updates that populated those columns.

See Part 2 for a continuation of this response.