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

I ran into something similar. I wanted to create essentially the same trigger for a bunch of tables. I ended up writing a SQL script to generate the trigger code for each table by reading the data dictionary. At least the code you end up with is more efficient than some convoluted generic code.

mgreaves_at_my-dejanews.com wrote:

> 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 - 23:06:52 CDT