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 sql*plus 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;
/