Re: Audit Trigger and Function

Erik Madsen wrote:

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

You seem to have made a great effort to overcomplicate a simple audit function ... and it should be an AFTER trigger ... not a BEFORE.

Why not just:

CREATE OR REPLACE TRIGGER WQDBA.AUDIT_ECM_TEST AFTER update or delete
ON ecm_test
FOR EACH ROW BEGIN
   INSERT INTO audit_table VALUES
   (....);

END; Just include sys_guid and user in the values inserted. There is no need for NDS.

Daniel Morgan Received on Mon Mar 31 2003 - 18:41:12 CST