Comparing :OLD and :NEW-Record in a Trigger

From: Thomas Stuefer <stuefer_at_halli-data.at>
Date: Thu, 24 Jul 2003 15:14:49 +0200
Message-ID: <3f1fdd10$1@e-post.inode.at>

I want compare a record in a Trigger with :OLD and :NEW and audit the changes in another table. But I don't want hardcode each field separate ...

The following Trigger uses EXECUTE IMMEDIATE for this problem and compiles successfully, but when firing it hangs and doesn't display any error or message.

Thank you for your help !

CREATE OR REPLACE TRIGGER tr_manipos_ko

BEFORE UPDATE ON manipos

FOR EACH ROW DECLARE    sVariableOld VARCHAR2(2000);

   sVariableNew VARCHAR2(2000);

   sSqlString VARCHAR2(250);

BEGIN    FOR recTabColumns IN (SELECT column_name

                         FROM   user_tab_columns

                         WHERE  table_name = 'MANIPOS') LOOP



      sSqlString := 'SELECT :OLD.' || recTabColumns.column_name || ', ' ||

                           ':NEW.' || recTabColumns.column_name || ' FROM

dual';

      EXECUTE IMMEDIATE sSqlString INTO sVariableOld, sVariableNew;

      IF sVariableOld <> sVariableNew THEN

• Do something like audit the changes in a table

         NULL;       END IF;    END LOOP; END; / Received on Thu Jul 24 2003 - 08:14:49 CDT