Help with Trigger

From: John Fico <fico_at_worldnet.att.net>
Date: 1997/03/22
Message-ID: <3333FDC5.85D@worldnet.att.net>#1/1

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 Sat Mar 22 1997 - 00:00:00 CST