How to create dynamic trigger in Oracle?

Thanks to http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:59412348055

Problem:

I’m trying to create a generic before update trigger
which will compare all :old.column_values to all
:new.column_values. If the column_values are different, then I
would like to log the change to a separate table. When I try to
compile :old., Oracle return an
“(1):PLS-00049: bad bind variable ‘NEW.” Can you recommend a
dynamic way to accomplish this? Thanks in advance.

Solution:

:new and :old are like bind variables to the trigger, they are not ‘regular’ variables.
you cannot dynamically access them, only ‘statically’.

I suggest you consider writing a stored procedure or sqlplus script to write a trigger
that statically references the new/old values. For example, if you wanted to save in a
table the time of update, who updated, table updated, column modified and new/old values,
you could code a sql
plus script like:

--------------------------------------------------------------------
create table audit_tbl
( timestamp date,
who varchar2(30),
tname varchar2(30),
cname varchar2(30),
old varchar2(2000),
new varchar2(2000)
)
/

create or replace package audit_pkg
as
procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 );

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in date,
l_old in date );

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in number,
l_old in number );
end;
/


create or replace package body audit_pkg
as

procedure check_val( l_tname in varchar2,
l_cname in varchar2,
l_new in varchar2,
l_old in varchar2 )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in date, l_old in date )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
l_new in number, l_old in number )
is
begin
if ( l_new l_old or
(l_new is null and l_old is not NULL) or
(l_new is not null and l_old is NULL) )
then
insert into audit_tbl values
( sysdate, user, upper(l_tname), upper(l_cname),
l_old, l_new );
end if;
end;

end audit_pkg;
/
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select ' audit_pkg.check_val( ''&1'', ''' || column_name ||
''', ' || ':new.' || column_name || ', :old.' ||
column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-------------

That will build the generic table and package plus generate a trigger that would look
like:

SQL> @thatscript dept


create or replace trigger aud#dept
after update on dept
for each row
begin
audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME);
audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC);
end;
/

Leave a Reply