Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

using dynamic sql in triggers with :OLD values

547817May 17 2007 — edited May 17 2007
i need to record all deleted rows from an entire schema in a single table. for that matter i created a function that receives a table name and generate an insert command according to it's primary key columns. i call this function in the table triggers. in order to insert the old values before the delete i use :OLD with "execute immediate" as followed :

create or replace trigger trg_some_tbl_bd
before delete on some_tbl
for each row is
declare
v_sql varchar2(4000);
begin
v_sql := generate_insert_command('some_table');
execute immediate v_sql;
end;

the return value from "generate_insert_command" function is the string:

insert into deleted_table (table_name , date , pk1 , pk2) values
('some_table' , sysdate , :OLD.pk1 , :OLD.pk2)


the execute immediate command notice the :OLD and looks for bind variables.
i need to know i can i bypass that. i tried looking for escape characters but couldent find any...

i would appriciate any help , it's kynda urgent
Thanks !
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 14 2007
Added on May 17 2007
5 comments
1,820 views