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

oracleoracle-11g-r2

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.

Best Answer

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

Related Question