Audit Trigger and Function

From: Erik Madsen <emadsenus_at_yahoo.com>
Date: 31 Mar 2003 14:27:21 -0800
Message-ID: <a523efe2.0303311427.63092fce@posting.google.com>

I'm trying to create a trigger that will handle any changes to the table without re-coding. I'm having some trouble understanding why this doesn't work...

create or replace FUNCTION "WQDBA".auditsql_fx(bindname IN VARCHAR2, destvar IN VARCHAR2)
RETURN VARCHAR2
IS
  returnme VARCHAR2(255);
BEGIN
         select 'select ' || bindname || ' into ' || destvar || ' from dual' into returnme from dual;

         RETURN(returnme);
END; and...

CREATE OR REPLACE TRIGGER "WQDBA".AUDIT_ECM_TEST before update or delete on ecm_test for each row declare

	   v_audit_id varchar2(35);
	   v_username varchar2(255);
	   cursor cur_cols is
	   		  select *
			  from	 dba_tab_columns
			  where	 table_name = 'ECM_TEST';
	   cur_row cur_cols%ROWTYPE;
	   oldval varchar2(4000);
	   newval varchar2(4000);
begin
	 select sys_guid() into v_audit_id from dual;
	 select user into v_username from dual;
	 open cur_cols;
	 loop
	 	 fetch cur_cols into cur_row;
		 exit when cur_cols%NOTFOUND;
		 


		 -- get old value

		 execute immediate auditsql_fx(':old.' || cur_row.column_name,

'oldval');                  

• get new value execute immediate auditsql_fx(':new.' || cur_row.column_name, 'newval');
• insert audit insert into wqdba.WQDBA_AUDIT ( audit_id, table_name, column_name, column_value, audit_datetime, user_name ) values ( v_audit_id, cur_row.table_name, cur_row.column_name, oldval, sysdate, v_username ); end loop; close cur_cols; end;

The error I am getting when attempting to update a row in ECM_TEST is

ERROR at line 1:

ORA-00905: missing keyword
ORA-06512: at "WQDBA.AUDIT_ECM_TEST", line 20
ORA-04088: error during execution of trigger 'WQDBA.AUDIT_ECM_TEST'

The dynamic SQL built with the function seems to execute, but with errors. If I actually hard code this generated SQL into the trigger, it runs fine. Received on Mon Mar 31 2003 - 16:27:21 CST