Using ":new" and ":old" in PL/SQL (Triggers)

Hi,

I am by no means an expert with PL/SQL, and have run into a problem that I can't seem to find an answer to in the documentation I have.

I am writing a trigger to keep track of changes to a table by writing out new and old values to an audit table. Within my code, I need to reference the new and old values, as in

        v_new_value := :new.column_name;

However, rather than have this line of code for each column, I would like to set up a loop, referencing the column name by having a cursor that reads each column name from the USER_TAB_COLUMNS data dictionary view. I think I have everything working, except that when I use a varible name, rather than an actual column name, as in

         v_column_name := 'COLUMN_NAME';
         v_new_value := :new.v_column_name;

I get this error message

         PLS-00049: bad bind variable 'NEW.V_COLUMN_NAME'

I think it is expecting an actual column name after the ":new.", rather than a variable. I am wondering if there is any way to get around this.

Thanks for any help on this matter!

Mark Greaves

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Aug 31 1998 - 17:23:25 CDT