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

[caiaphas]

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( updatedtablenam e )

for cont 1..colnames.con t

squery:='insert into Auditor_table(c ol1,col2,col) values (:new.'||colnam es(cont)||',old .'||colnames(co nt)||','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?

[masdi2t]

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( updatedtablenam e )

for cont 1..colnames.con t

squery:='insert into Auditor_table(c ol1,col2,col) values (:new.'||colnam es(cont)||',old .'||colnames(co nt)||','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_na me;
sQuery VARCHAR2(1000);
BEGIN
FOR data IN cur LOOP
-- double single quote in UPDATE text
sQuery := 'INSERT INTO auditor_table VALUES (' || ':NEW.' || data.column_nam e || ', :OLD.' || data.column_nam e || ', ''UPDATE'')';
EXECUTE IMMEDIATE sQuery;
END LOOP;
END;