Home » SQL & PL/SQL » SQL & PL/SQL » Dynamically accessing columns of :NEW in trigger (Oracle 10g (9.0.4))
Dynamically accessing columns of :NEW in trigger [message #328572] Fri, 20 June 2008 09:18 Go to next message
zumbo
Messages: 1
Registered: June 2008
Junior Member
In a trigger, I want to execute the same statement for several columns of the record that triggered the event. Do I have to copy the code for every column (there are lots of columns, and there's a lot more code than what you see below), or is there a way to solve this with dynamic SQL?

I tried something like that:
(There's a loop around that, with the variable "column_name" set to each individual column name)

EXECUTE IMMEDIATE 'INSERT INTO AUDIT_TRAIL (NEW_VALUE) VALUES (:NEW.'||column_name||')';

But it won't work because :NEW isnn't bound within the dynamically created statement.

When I rewrite it to:

EXECUTE IMMEDIATE 'INSERT INTO AUDIT_TRAIL (NEW_VALUE) VALUES (:1.'||column_name||')' USING :NEW;

It won't compile; I'm getting "PLS-00049: bad bind variable 'NEW'".



Re: Dynamically accessing columns of :NEW in trigger [message #328582 is a reply to message #328572] Fri, 20 June 2008 09:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There is no such bind variable as :NEW

There are a set of variable, one for each column, called :NEW.<column name>, but no record called :NEW.

One statement per column, I'm afraid.

[Updated on: Fri, 20 June 2008 09:37]

Report message to a moderator

Re: Dynamically accessing columns of :NEW in trigger [message #328719 is a reply to message #328572] Sun, 22 June 2008 04:28 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Why would you use 'Execute Immediate' for a DML statement....
Re: Dynamically accessing columns of :NEW in trigger [message #328723 is a reply to message #328572] Sun, 22 June 2008 06:22 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
he is trying to build an auditing system without having to write a trigger for each table

why not write a script that creates does trigger by looping through all your tables you want to audit, the table then becomes a variable and you can then generate code that loops through it's colmuns (select column_name from user_tab_columns where....)


Re: Dynamically accessing columns of :NEW in trigger [message #328740 is a reply to message #328572] Sun, 22 June 2008 09:41 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055
Previous Topic: Data from table having BFILE column
Next Topic: Need Help With Simple PL/SQL Script
Goto Forum:
  


Current Time: Sat Dec 10 01:18:20 CST 2016

Total time taken to generate the page: 0.10017 seconds