Referencing :NEW AND :OLD values of a particular column in a trigger

From: Ken Grissom <Ken_G_at_cyberram.com>
Date: Sun, 13 Jun 1999 09:14:28 -0500
Message-ID: <8D28E6A6C26EE3ED.A676D273B1676732.8FECC0E2162D9525@lp.airnews.net>

Howdy,

Is there a way to reference new and old values of a particular column without hard codeing the name of the particular column? Here is some sample code that of course does not work.

CREATE OR REPLACE TRIGGER PLAY_TRIG
  BEFORE UPDATE ON PLAY
  FOR EACH ROW DECLARE
   CURSOR Fields IS

      SELECT COLUMN_NAME
        FROM USER_TAB_COLUMNS
       WHERE Table_NAME = 'PLAY';
   C1              Integer;
   C1_CNT          NUMBER;
   SQLSTRING       VARCHAR2(200);
BEGIN
     DBMS_OUTPUT.ENABLE(10000);
            FOR FIELD_REC IN FIELDS LOOP
               IF UPDATING (Field_Rec.COLUMN_NAME) THEN
                          SQLSTRING := 'INSERT INTO AUDIT_PLAY (AUDIT_TIME,
PLAY) VALUES ('||SYSDATE||', '||:NEW.||'*REPLACE*)';
                      -- Also tried
                     --                          SQLSTRING := 'INSERT INTO

AUDIT_PLAY (AUDIT_TIME, PLAY) VALUES ('||SYSDATE||', :NEW.*REPLACE*)';

                          SQLSTRING :=
REPLACE(SQLSTRING,'*REPLACE*',Field_Rec.COLUMN_NAME);
                 DBMS_OUTPUT.PUT_LINE(SQLSTRING);
                          --DBMS_SQL.Parse(C1, SQLSTRING, DBMS_SQL.NATIVE);
                           --C1_CNT := DBMS_SQL.Execute(C1);
               END IF;
            END LOOP; -- End CURSOR LOOP FIELDS

END; -- End Play trig
/

Show Errors Trigger PLAY_Trig

Thanks,
Ken Received on Sun Jun 13 1999 - 09:14:28 CDT