Re: How do I write generic code in triggers?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/09
Message-ID: <34b786ab.15138467@inet16>

On Fri, 09 Jan 1998 17:01:56 +0100, Woodrow <woodrow_at_nlr.nl> wrote:

>I would like to write a generic logging routine in an after_update
>trigger. This routine should write a logging message for every updated
>attribute of a table. The snag is that I do not want to code the names
>of these attributes but determine them run-time.
>
>The following code example determines what attributes there are in the
>table scott.emp. Within the loop the old and new values of each
>attribute should be compared and when they differ, a logging message
>should stored.
>
>TRIGGER "AFTER_UPDATE_EMP" AFTER UPDATE ON SCOTT.EMP
>FOR EACH ROW
>DECLARE
>
> ignore INTEGER;
>
> select_cursor INTEGER;
> column_name VARCHAR2(30);
> data_type VARCHAR2(9);
> column_id NUMBER;
>
> name_of_column_old VARCHAR2(30);
> name_of_column_new VARCHAR2(30);
>
>BEGIN
>
> ------------------------------------------
> -- prepare a cursor and columns to select from the
> -- all_tab_columns table the names and the data types of
> -- all attributes of the currently handled table
> ------------------------------------------
> select_cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE( select_cursor,
> 'SELECT column_name, data_type, column_id
> FROM all_tab_columns
> WHERE table_name = ''EMP''',
> DBMS_SQL.V7);
> ignore := DBMS_SQL.LAST_ERROR_POSITION;
>
> DBMS_SQL.DEFINE_COLUMN(select_cursor, 1, column_name, 30);
> DBMS_SQL.DEFINE_COLUMN(select_cursor, 2, data_type, 9);
> DBMS_SQL.DEFINE_COLUMN(select_cursor, 3, column_id);
> ignore := DBMS_SQL.EXECUTE(select_cursor);
>
>
> ----------------------------------------------------
> -- select from the all_tab_columns table the names and the
> -- data types of all attributes of the currently handled
> -- table and for each table attribute check if
> -- new != old and if so, store a logging message
> ----------------------------------------------
> LOOP
> IF (DBMS_SQL.FETCH_ROWS(select_cursor)>0) THEN
>
> -- get the attribute name, data type
> DBMS_SQL.COLUMN_VALUE( select_cursor,
> 1,
> column_name);
> DBMS_SQL.COLUMN_VALUE( select_cursor,
> 2,
> data_type);
> DBMS_SQL.COLUMN_VALUE( select_cursor,
> 3,
> column_id);
>
> name_of_column_new := ':old.' || column_name;
> name_of_column_new := ':new.' || column_name;
>
>-- !!!So far everythings oke but here the problem
>-- !!!begins
>--
>-- I would now like to do something like:

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

---

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

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 Fri Jan 09 1998 - 00:00:00 CST