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_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