CREATE OR REPLACE PROCEDURE CREATE_AUDIT_TRIGGER (v_table_name IN VARCHAR2, — Table Name.
v_enabled IN VARCHAR2 DEFAULT ‘Y’) — Enable Trigger on creation?
IS
/*
DESCRIPTION:
This procedure creates a standard audit trigger for a standard audit table.

REVISIONS:
Ver Date Author Description
——— ———- ————— ————————————
1.0 19/06/2002 DRC 1. Created this procedure.

PARAMETERS:
NOTES:
Make sure owner has CREATE ANY TRIGGER & CREATE ANY SEQUENCE privilege.
*/

sql_string VARCHAR2(4000);
sql_ddl_string VARCHAR2(4000); — Added due to bug Doc ID: 104059.1
v_column_name VARCHAR2(30);
v_audit_table_name VARCHAR2(30) DEFAULT v_table_name || ‘_AUDITS’;
v_transaction_id_seq_name VARCHAR2(30) DEFAULT v_table_name || ‘_AUD_SEQ’;
v_trigger_name VARCHAR2(30) DEFAULT v_table_name || ‘_TRIG’;
v_seq_created VARCHAR2(1);

CURSOR get_table_info IS
select column_name
from user_tab_columns
where table_name = v_table_name
order by column_id;

CURSOR get_audit_table_info IS
select column_name
from user_tab_columns
where table_name = v_audit_table_name
order by column_id;

BEGIN

— Create sequence for Transaction ID first.
BEGIN

SELECT ‘X’
INTO v_seq_created
FROM USER_SEQUENCES
WHERE SEQUENCE_NAME = v_transaction_id_seq_name;

EXCEPTION
WHEN no_data_found THEN

sql_string := ‘CREATE SEQUENCE ‘ || v_transaction_id_seq_name || ‘ START WITH 0 NOMAXVALUE NOCYCLE NOCACHE MINVALUE 0’;

sql_ddl_string := sql_string;

execute immediate sql_ddl_string;

WHEN others THEN
dbms_output.put_line(‘Error creating sequence. ‘|| sqlerrm);
END;

sql_string := ‘CREATE OR REPLACE TRIGGER ‘ || v_trigger_name || chr(10) ||
‘ BEFORE INSERT or DELETE or UPDATE’ || chr(10) ||
‘ ON ‘ || v_table_name || chr(10) ||
‘ FOR EACH ROW’ || chr(10) ||
‘ DECLARE’ || chr(10) ||
‘ trans_id number;’ || chr(10) ||
‘ trans_type varchar2(6);’ || chr(10) ||
‘ today date;’ || chr(10) ||
‘ begin’ || chr(10) ||
‘ select sysdate into today from dual;’ || chr(10) ||
‘ if INSERTING then’ || chr(10) ||
‘ trans_type := ”INSERT”;’ || chr(10) ||
‘ elsif DELETING then’ || chr(10) ||
‘ trans_type := ”DELETE”;’ || chr(10) ||
‘ elsif UPDATING then’ || chr(10) ||
‘ trans_type := ”UPDATE”;’ || chr(10) ||
‘ end if;’ || chr(10) ||
‘ select ‘ || v_transaction_id_seq_name || ‘.nextval into trans_id from dual;’ || chr(10) ||
‘ insert into ‘ || v_audit_table_name || ‘ values( trans_id,’ || chr(10);

–Now for the value columns
OPEN get_table_info;
<>
LOOP

v_column_name := NULL;

FETCH get_table_info INTO v_column_name;

IF get_table_info%NOTFOUND THEN
EXIT code_loop3;
END IF;

sql_string := sql_string || ‘ :new.’ || v_column_name || ‘,’ || chr(10);

END LOOP;
CLOSE get_table_info;

–Now for the value columns
OPEN get_table_info;
<>
LOOP

v_column_name := NULL;

FETCH get_table_info INTO v_column_name;

IF get_table_info%NOTFOUND THEN
EXIT code_loop2;
END IF;

sql_string := sql_string || ‘ :old.’ || v_column_name || ‘,’ || chr(10);

END LOOP;
CLOSE get_table_info;

sql_string := sql_string || ‘ user, trans_type, sysdate); ‘ || ‘end;’;

sql_ddl_string := sql_string;

execute immediate sql_ddl_string;

dbms_output.put_line(‘Audit Table Trigger ‘ || v_trigger_name || ‘ Created.’);

— Enable or Disable Trigger

IF v_enabled = ‘Y’ THEN

sql_string := ‘ALTER TRIGGER ‘ || v_trigger_name || ‘ ENABLE’;

ELSE

sql_string := ‘ALTER TRIGGER ‘ || v_trigger_name || ‘ DISABLE’;

END IF;

sql_ddl_string := sql_string;

execute immediate sql_ddl_string;

dbms_output.put_line(‘Audit Table Trigger ‘ || v_trigger_name || ‘ Created.’ || v_enabled );

EXCEPTION
WHEN others THEN
dbms_output.put_line(sqlerrm);

END;
/

How to create a dynamic Oracle audit trigger

Leave a Reply

Your email address will not be published. Required fields are marked *

+ 34 = forty four