Interesting Problem Regarding Triggers

From: John Fico <fico_at_worldnet.att.net>
Date: 1997/03/12
Message-ID: <33275A8D.5555_at_worldnet.att.net>#1/1

I am trying to create a trigger that will accoplish field level auditing. The final solution must be able to insert a record into an audit table and be able to track changes to columns in a very sensitive table. I must track every change to every column. The sensitive table "LOGO_MAIN" has aprox 20 columns and this could increase.

The long way of acomplishing this would be to hard code each column into the trigger and check :new.col_name vs. :old.col_name and if they are different then insert a record into the audit table. Every column that changes should generate a seperate record.

The way that I want to to this is as follows:

CREATE OR REPLACE TRIGGER TRG_LOGO_MAIN_AUDIT AFTER UPDATE
ON LOGO_MAIN
for each row
DECLARE

	ACTION 		CHAR := 'M';
	CURSOR		LOG_CURSOR IS SELECT COLUMN_NAME
				FROM USER_TAB_COLUMNS WHERE TABLE_NAME='LOGO_MAIN';
	NEW_VALUE   VARCHAR2(50);
	OLD_VALUE   VARCHAR2(50);
	FIELD_NAME  VARCHAR2(25);
	LOGO_NAME   VARCHAR2(8);

BEGIN
-- log the change to LOGO_UPDATE_LOG file FOR LOG_REC IN LOG_CURSOR LOOP

  IF UPDATING( LOG_REC.COLUMN_NAME) THEN  		-- this line works fine
    NEW_VALUE := ':new.' || LOG_FIELDS_REC.FIELD_NAME; -- this does not
    OLD_VALUE := ':old.' || LOG_FIELDS_REC.FIELD_NAME; -- this does not

  INSERT INTO LOGO_UPDATE_LOG VALUES( NULL, NULL, :new.LOGO, ACTION, LOG_REC.COLUMN_NAME, NEW_VALUE, OLD_VALUE);   END IF;
END LOOP;
END; I need a way to get the new and old values into their respective variables so that they can be inserted into the logo_update_log table (last two fields).

I've even gone so far as to try to use the dbms_sql package to dynamically create the insert string. (Which I was able to do) Unfortunately when I tried to execute for instance :

        insert into logo_update_log values ( NULL, NULL, :new.LOGO, 'M', 'SYSTEM_ID', :old.system_id,:new.system_id );

it gave back a error stating that bind variables (presumably :new and :old) were not defined.

        Any help on this would be greatly appreciated.

                Thank you very much,

			John Fico
			fico_at_worldnet.att.net

Received on Wed Mar 12 1997 - 00:00:00 CET