I am trying to write some logging functionality. The basic problem is that there are a large number of tables where changes to row content need to be logged. In each case, I need to compare old and new values and log only the changes. I do not want to write a trigger for each table specifying the column names as this would be a nightmare with respect to maintenance (unless I could use something like a "AFTER CREATE OR ALTER ON" trigger to rewrite the log triggers?). The ideal solution would be to pass new and old to a SP that could go though the names/old values/new values and create the log entries. But, as far as I can see, the old/new values cannot be handled as a sort of array. It seems that the only option is to use the syntax :old.colname. Any ideas would be appreciated.

    Requires Free Membership to View

    Login

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.


This was first published in October 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top