Re: Generic update trigger for auditing changed columns in a table.

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 16 May 1999 00:25:07 GMT
Message-ID: <373e1041.11449363_at_newshost.us.oracle.com>


A copy of this was sent to "Dave Akin" <dakin_at_chis.com> (if that email address didn't require changing) On Fri, 14 May 1999 17:17:28 -0700, you wrote:

>I'd like to set up a generic update trigger to check the columns in the
>table to see if they've been updated or not. If they have I'd like to
>write that particular column's value and column name out to an audittable.
>This is what I have so farDECLARE p_owner_out VARCHAR2(30);
> p_table_name_in VARCHAR2(30) := 'CERTIFICATIONS';
> v_jrn_flag VARCHAR2(1) := NULL; CURSOR c_columns IS
> SELECT column_name, data_type FROM all_tab_columns
> WHERE table_name = p_table_name_in --name of table
> AND owner = p_owner_out; --owner of table BEGIN
>p_TABLE_OWNER(p_table_name_in, p_owner_out); --proc to retrieve tableowner
>FOR column_rec IN c_columnsLOOP IF UPDATING(column_rec.column_name) THEN
> INSERT INTO change_journals( operation
> , schema_name
> , table_name
> , primary_key_value
> , column_name
> , textual_value) VALUES
> ( 'U' ,v_owner
> ,p_in_tbl_name
> ,:new.certification_id
> ,column_rec.column_name
> ,?????? to_char(:old.( value ofcolumn_name);
>END LOOP;END;
>What I cannot seem to figure out is how to dynamically reference the
>value of the :new.xxxxx bind variable the is represented in the cursorby
>column_rec.column_name.Any suggestions???????
>

you cannot dynamically reference the :new and :old bind variables.

the way people typically do this is to use SQL to write the trigger itself -- instead of dynamically at run time trying to figure out what the columns are, you would dynamically at compile time build the trigger from the data dictionary. Here is a previous posting of mine on this subject:

You can do this, but not in the way you were attempting. You cannot dynamically access the :new and :old values in a trigger. What you can do though to reduce the coding you have to do is to write a procedure to generate the trigger for a given table. The following demonstrates the idea and sets up the triggers for EMP and DEPT (and note save_changes below, you cannot just compare old and new using <>, you must also consider NULLS as well in the comparision):

create table changes

( tname varchar2(30),
  cname varchar2(30),
  oldv  varchar2(2000),
  newv  varchar2(2000),

  timestamp date
);  
create or replace procedure save_changes( p_tname in varchar2,
                                          p_cname in varchar2,
                                          p_oldv  in varchar2,
                                          p_newv  in varchar2 )
is
begin  

    if ( p_oldv <> p_newv or

         ( p_oldv is not null and p_newv is null ) or
         ( p_oldv is null and p_newv is not null ) )
    then
        insert into changes ( tname, cname, oldv, newv, timestamp )
        values ( p_tname, p_cname, p_oldv, p_newv, sysdate );
    end if;
end save_changes;
/  

create or replace procedure gen_trigger( p_tname in varchar2 ) as

    l_tname varchar2(30) default upper(p_tname);     l_stmt varchar2(255) default
    'save_changes( ''' || l_tname || ''', ''$C$'', :old.$C$, :new.$C$ );'; begin

    dbms_output.put_line( 'create or replace trigger audit_' || l_tname );
    dbms_output.put_line( 'after update on ' || l_tname );
    dbms_output.put_line( 'for each row' );
    dbms_output.put_line( 'begin' );
    for x in ( select column_name
                 from user_tab_columns
                where table_name = l_tname )
    loop
        dbms_output.put_line( chr(9)||replace(l_stmt,'$C$', x.column_name));
    end loop;
    dbms_output.put_line( 'end;' );
    dbms_output.put_line( '/' );
end;
/

set serveroutput on
set feedback off

spool tmp.sql
exec gen_trigger('emp')
exec gen_trigger('dept')
spool off

set feedback on
_at_tmp.sql



so, in the above, the calls to gen_trigger will actually just generate the trigger code. By spooling that output to tmp.sql and then running tmp.sql, we get the generic trigger we need.

If you granted your self "create trigger", you could actually have the gen_trigger routine create the trigger instead of just printing the trigger out to be created...

An enhancement to this would have you move the procedure save_changes into a package and overload it so that it worked better for dates and numbers (eg; it would to_char(thedate, 'dd-mon-yyyy hh24:mi:ss') before inserting it as a varchar so as to not lose the precision of the date....

Hope this helps..  

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Sun May 16 1999 - 02:25:07 CEST

Original text of this message