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.
before insert or ...
2. Remove the redundant linereferencing ...
, it's not needed. 3. I can't find any documentation forinserting(<column_name>)
; I'm pretty sure that only works forupdating(<column_name>)
.