2

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.

2
  • 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>). Feb 13, 2014 at 18:53
  • Actually the trigger will fire 'after insert'. I have removed 'referencing' as you have advised but I am stuck with the error. Feb 14, 2014 at 9:43

2 Answers 2

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

-1

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

1
  • 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. Feb 14, 2014 at 9:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.