Re: [Q] Can it be done in a trigger?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/06
Message-ID: <34899a83.33871775@inet16>

On Fri, 05 Dec 1997 13:36:45 -0600, Simon_Goland_at_notes.bctel.com wrote:

>Hi,
>
>I am writing a set of triggers to keep a log of ALL changes to ALL
>fields in almost all the tables of our database. The easiest is to do
>something like (simplified here, just to illustrate the concept):
>
>CREATE OR REPLACE TRIGGER TableA_log_bu
>BEFORE UPDATE ON TableA
>FOR EACH ROW
>DECLARE
>BEGIN
> -- Log changes to every field where the new value
> -- is different from the old one.
> --
> IF :new.ColumnA <> :old.ColumnA THEN
>
> INSERT INTO ChangeHistory VALUES (
> ChangeHistory_seq.NextVal,
> :old.ColumnA,
> :new.ColumnA,
> SysDate);
> END IF;
>

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
@tmp.sql

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...

>However, this is not the most efficient, elegant, or easiest to maintain
>solution, because I have to repeat this code for every column in every
>table.
>
>What I am thinking of doing now is create an array (a table) which will
>contain a list of columns per table. This I can obtain using
>
>select column_name
> from user_tab_columns
> where table_name = 'TABLEA';
>
>Then I iterate through this table, and perform a similar check for every
>column. So it is dynamic, and I don't need to worry if a table structure
>changes. So the code will look similar to the following:
>
>CREATE OR REPLACE TRIGGER TableA_log_bu
>BEFORE UPDATE ON TableA
>FOR EACH ROW
>DECLARE
> TYPE ColumnListTableType IS TABLE of
> user_tab_columns.column_name%TYPE INDEX BY BINARY_INTEGER;
> vColumnList ColumnListTableType;
> vColumnName user_tab_columns.column_name%TYPE;
> i BINARY_INTEGER := 0;
>BEGIN
>
> FOR vColumnName IN
> (SELECT column_name
> FROM user_tab_columns
> WHERE table_name = 'DISCOUNTPROFILE') LOOP
> i := i + 1;
> vColumnList(i) := vColumnName;
> END LOOP;
>
> -- Log changes to every field where the new value
> -- is different from the old one.
> --
> FOR i IN 1 .. vColumnList.COUNT LOOP
>
> IF :old.vColumnList(i) <> :new.vColumnList(i) THEN
> INSERT INTO ChangeHistory VALUES (
> ChangeHistory_seq.NextVal,
> :old.vColumnList(i),
> :new.vColumnList(i),
> SysDate);
> END IF;
> END LOOP;
>
>And the problem is with :old.vColumnList and :new.vColumnList - Oracle
>doesn't like this type of usage or reference. What am I missing? Is there
>another way of accomplishing this task?
>
>Any idea/tip/advice would be greatly appreciated. And if you can email,
>in addition to posting...
>
>Simon
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Dec 06 1997 - 00:00:00 CST