473,322 Members | 1,480 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

using :NEW and :OLD in Dynamic SQL URGENT!!

hi,
Im trying to make a trigger that monitors the deletes and updates on a table. I will use this design for many tables so Im trying to make it as dynamic as posible.
Im trying to build a dynamic query the following way:

colnames is an array(datatype)
squery varchar(1000)

colnames:= getcolnames( updatedtablename )

for cont 1..colnames.cont

squery:='insert into Auditor_table(col1,col2,col) values (:new.'||colnames(cont)||',old.'||colnames(cont)|| ','UPDATE' )';

execute inmmediate squery

I get an error on executing time telling me that the variable was not bound,
i dont know if dynamic SQL is able to get the values of :new or :old like this

any body have an idea? or a different way to do this?
Apr 10 '07 #1
1 6524
masdi2t
37
hi,
Im trying to make a trigger that monitors the deletes and updates on a table. I will use this design for many tables so Im trying to make it as dynamic as posible.
Im trying to build a dynamic query the following way:

colnames is an array(datatype)
squery varchar(1000)

colnames:= getcolnames( updatedtablename )

for cont 1..colnames.cont

squery:='insert into Auditor_table(col1,col2,col) values (:new.'||colnames(cont)||',old.'||colnames(cont)|| ','UPDATE' )';

execute inmmediate squery

I get an error on executing time telling me that the variable was not bound,
i dont know if dynamic SQL is able to get the values of :new or :old like this

any body have an idea? or a different way to do this?

try this
CREATE TRIGGER ......
....
DECLARE
CURSOR cur IN SELECT column_name FROM user_tab_cols WHERE table_name = ora_dict_obj_name;
sQuery VARCHAR2(1000);
BEGIN
FOR data IN cur LOOP
-- double single quote in UPDATE text
sQuery := 'INSERT INTO auditor_table VALUES (' || ':NEW.' || data.column_name || ', :OLD.' || data.column_name || ', ''UPDATE'')';
EXECUTE IMMEDIATE sQuery;
END LOOP;
END;
Apr 13 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Jochen Luig | last post by:
Hi! I think I don't grasp the concept of "new". I tried to create a 2-dimensional array using new (see code below). What I don't understand is, why does the program below work while the lines I...
11
by: z_learning_tester | last post by:
Hello, yes another beginner question that I'm sure is obvious to many here :-) My book is so bad. Really. It uses the exact same example of code for using the new kw and for using virtual(in the...
1
by: Paul E Collins | last post by:
I am writing a class that inherits from TextBox. My class, UndoTextBox, extends the standard control by enabling multi-level undo and redo operations. Annoyingly, some of the affected properties...
6
by: Robert Schuldenfrei | last post by:
Dear NG, As expected, when I went to implement TIMESTAMP, I failed. With the help of Kevin Yu, I got the 2 code segments at the bottom working using a WHERE clause that checks all columns. ...
0
by: Colin Fox | last post by:
I've got a trigger function that gets executed after an insert or an update. It basically just creates a copy of the row in a history table. However, there are cases when a row will be updated to...
34
by: John | last post by:
This produces an initialized array to zero: int *i = new int() ; 004124B0 push ebp 004124B1 mov ebp,esp 004124B3 mov eax,dword ptr 004124B6 push eax...
7
by: aruna.mysore | last post by:
Hi all, Is it possible to allocate an object on stack in C++ using new() operator. Thanks in advance, Aruna
8
by: Chris Portka | last post by:
I need to be able to allocate large numbers of elements at a time but then delete them one at a time. I have settled on the design of using new to allocate many items at once, but am unsure what...
13
by: stephenpas | last post by:
We are trying to monkey-patch a third-party library that mixes new and old-style classes with multiple inheritance. In so doing we have uncovered some unexpected behaviour: <quote> class Foo:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.