Re: Generic update trigger for auditing changed columns in a table.
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 CorporationReceived on Sun May 16 1999 - 02:25:07 CEST