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;
/