Re: Dynamically referencing fieldnames in table trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 18 Aug 1999 11:54:22 GMT
Message-ID: <37bc9e56.176081031@newshost.us.oracle.com>

A copy of this was sent to Fraser Boswell <Fraser.Boswell_at_spamme.ed.ac.uk> (if that email address didn't require changing) On Wed, 18 Aug 1999 09:27:50 +0100, you wrote:

>Tried badly to explain myself last time so I'll try again. Here's the
>code body for an After Update Trigger
>
>Begin
> declare
> cursor c is
> select colname
> from my_table_columns
> where tablename = 'MY_TABLE'
> and auditflag = 'Y'; -- so cursor c brings back a list of all
> --fieldnames to be checked in case they have been
>updated
> currcolname table_columns.colname%type; -- to hold the field name
> begin
> for my_rec in c
> loop
> -- I WANT TO WRITE THE CODE BELOW
> if :NEW.currcolname != :OLD.currcolname then
> --do stuff
> end if;
> -- UP TO HERE
> end loop;
> end;
>End;
>
>Now if currcolname is Surname I need the line to read
> if :NEW.Surname != :OLD.Surname then
>instead of currcolname. So the IF statement needs to be dynamic.
>Any ideas
>
>Thanks in advance
>
>Fraser
>

This is from a previous posting of mine. It shows one method of doing this. Your "do stuff" needs to be in the check val routine. This method (below) works correctly with NULLs and the three basic datatypes of Strings, Numbers and Dates.

 :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||l_old is not 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||l_old is not 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||l_old is not 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;
/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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 Wed Aug 18 1999 - 06:54:22 CDT