Bad bind variable in audit trail trigger in Oracle 11g R2

Question

I have been trying to construct audit trail triggers and procedures in oracle and I am facing a problem with bind variables. Following is the code

create or replace trigger trg_audit_trail
for insert or update or delete 
on tbl_mst_books
referencing NEW AS New OLD AS Old
for each row
declare
 colName VARCHAR2(50);
 cursor audit_cur is
 select column_name from all_tab_columns where table_name = 'tbl_mst_books';
begin
 for rec_audit in audit_cur loop
 colName := rec_audit.column_name;
 if inserting then
 if (inserting(colName)) then
 prc_audit_trail('tbl_mst_books',colName,:new.colName); /*stored procedure call
 to insert audit trail data from multiple triggers to audit trail table */
 end if;
 end if;
 end loop;
end trg_audit_trail;

The stored procedure prc_audit_trail is now inserting the values passed as parameters into audit trail tables. The problem I am having is with the bind variable :new. I am getting an error of bad bind variable. Apparently I am not being able to do :new.colName but instead of colName if I directly write the name of the column(eg: BOOK_NAME or BOOK_AUTHOR) it's not giving any error. Since I have to fetch the column name of the affected column I am fetching the column name to a variable and trying to do :new.colName. How can I resolve the issue?? Please help me. Also can I get column names of the affected columns(during insert, update and delete) in some other way? Why am i getting the bad bind variable error for :new.colName? How can I resolve it??

P.S. I have just shown the portion for IF INSERTING but I am following the same process for IF UPDATING in the same trigger where I am getting the same error also for :old.colName

Thank you.

• Colin 't Hart: 1. I presume that should be before insert or ... 2. Remove the redundant line referencing ..., it's not needed. 3. I can't find any documentation for inserting(<column_name>); I'm pretty sure that only works for updating(<column_name>).
• Subhaprasad Mukherjee: Actually the trigger will fire 'after insert'. I have removed 'referencing' as you have advised but I am stuck with the error.

Answer 1

If you write :new.colName than you are referencing the new value of a column named "COLNAME" of table tbl_mst_books. But table tbl_mst_books has no column named "COLNAME". Actually there is no possibility to do this this way because dynamic sql does not work with :new and :old bind values. You have to program it explicitly for every column in your table and have to change your code if your table structure changes.

see AskTom

Answer 2

Try removing the ':' because new is not a bind variable.

• Subhaprasad Mukherjee: Removing ':' is not working either. I guess it will not work if I assign column name dynamcicall to a variable and use it like that. Is there any other way to insert column names dynamically as the business demands it.