Want to know what table/column changed in ORACLE bfore posting (commit 1

[hasfari]

I want to write a generic trigger and use it for some database tables in my database to create kind of transaction log record in the transaction log table. This trigger needs to be generic if possible and on the database level instead of one trigger for each database table.

This trigger is a pre (insert, update or delete) one on the database level. The trigger needs to know about what column and table were changed in the database before the actual insert, update or delete takes place.

Is there any way in ORACLE to tell what table/column got dirty (changed) and trigger that in this trigger.

 

[jee]

First of all, I think you are going to have to use a trigger per table. Database-level triggers only fire in response to system events like database startup or shutdown, etc.

You can do this in a way that is fairly generic. First of all you can setup up a procedure/function (or more than one) to do the work, with input parameters like table_name, column_name, old_value, new_value. On a couple of my projects, I used a package so I could define overloaded functions, one for each possible datatype that my columns could have.

Then you can write a script that will automatically generate your triggers. For instance here is the script I used on one project:

SET verify OFF

/* Prepare to output to a temporary file */
SET feedback OFF
SET serveroutput ON SIZE 1000000
SET termout OFF 
spool TEMP.SQL

/* Create the temporary script file that will create the triggers */
DECLARE
   v_trigger_name       VARCHAR2 (30);
   v_return             VARCHAR2 (20);
   v_first_time         BOOLEAN;
   
   /* Cursor to step through all tables owned by the current user
      with exceptions */
   CURSOR table_cursor IS
      SELECT table_name
        FROM USER_TABLES
       WHERE table_name != ('TABLE_JOURNAL')
         AND table_name != ('COLUMN_JOURNAL')
         AND table_name != ('SEAL_EVENT')
         AND table_name != ('SECURITY_SEAL')
       ORDER BY table_name;
       
   /* Cursor to step through all columns in the specified table 
      with exceptions */
   CURSOR column_cursor (c_table   USER_TABLES.table_name%TYPE) IS
      SELECT column_name, data_type
        FROM USER_TAB_COLUMNS
       WHERE table_name = c_table
         AND column_name NOT LIKE ('SEAL_EVENT_ID%')
       ORDER BY column_id;   
          
BEGIN

   FOR table_rec IN table_cursor LOOP
      /* Trigger name can be no longer than 30 chars */
      v_trigger_name := SUBSTR (table_rec.table_name, 1, 24) || '_audit';

      /* Create the top of the create or replace trigger statement */
      dbms_output.put_line ('CREATE OR REPLACE TRIGGER ' || v_trigger_name);
      dbms_output.put_line ('AFTER INSERT OR UPDATE OR DELETE ON ' ||
         table_rec.table_name);
      dbms_output.put_line ('FOR EACH ROW');
      /* Add When clause to prevent auditing if nothing changes */
      dbms_output.put_line ('WHEN (');
      v_first_time := TRUE;
      FOR col_rec IN column_cursor (table_rec.table_name) LOOP
         IF (col_rec.data_type = 'NUMBER') THEN
            v_return := '0';
         ELSIF (col_rec.data_type  = 'DATE') THEN
            v_return := 'sysdate - 100000';
         ELSE
            v_return := ''' ''';
         END IF;
         IF (v_first_time) THEN
            dbms_output.put_line ('NVL (NEW.' || col_rec.column_name ||
               ', ' || v_return || ') != NVL (OLD.' || col_rec.column_name || 
               ', ' || v_return || ')');
            v_first_time := FALSE;
         ELSE
            dbms_output.put_line ('OR NVL (NEW.' || col_rec.column_name ||
               ', ' || v_return || ') != NVL (OLD.' || col_rec.column_name || 
               ', ' || v_return || ')');
         END IF;   
      END LOOP;
      dbms_output.put_line (')');    
      dbms_output.put_line ('BEGIN');
      dbms_output.put_line ('audit_changes.audit_table (''' || 
         table_rec.table_name || ''');');
         
      /* Add auditing of each column in the table */
      FOR col_rec IN column_cursor (table_rec.table_name) LOOP
         dbms_output.put_line ('audit_changes.audit_column (''' ||
            col_rec.column_name ||''', :OLD.' || 
            col_rec.column_name || ', :NEW.' || col_rec.column_name || ');');
      END LOOP;
      
      /* End the create or replace trigger statement */
      dbms_output.put_line ('END;');
      dbms_output.put_line ('/');
   END LOOP;
END;
/

/* Put stuff back the way it was */
spool OFF
SET termout ON
SET serveroutput OFF
SET feedback ON    

/* Now create the triggers */
@TEMP

/* And delete the temporary trigger creation script file */
host del TEMP.SQL
[\code]

I could have used the UTL_FILE package instead of DBMS_OUTPUT to create my temporary script, but I was running this on a database that had no UTL_FILE parameter defined in the INIT.ORA file.

Note that the generated triggers will have WHEN clauses that will prevent any action if nothing actually changes.

I generated "AFTER" triggers, rather than "BEFORE", but if you want to audit before, you can do that.

The audit_changes.audit_column procedure can compare the before and after values and do nothing if they are the same.