Building an Audit Trail for Your Data - Creating the Audit Triggers
(Page 4 of 6 )
Once audit tables exist, a second script is used to create triggers that fire whenever inserts, updates, or deletes occur on the main table.
The code for audit_triggers.sql is below. As you will notice, it uses many of the same constructs used in the audit_tables.sql. Two cursors are defined for each table name and definition, then processed.
--audit_triggers.sql creates (or replaces) triggers on audit tables.
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SPOOL build_audit_triggers.sql
DECLARE
v_prefix VARCHAR2(5) := NULL;
v_condition VARCHAR2(30) := NULL;
--Select all user tables with a corresponding audit table.
CURSOR cur_tbl2audit IS
SELECT table_name
FROM user_tables a
WHERE table_name NOT LIKE '%$AUD'
AND EXISTS
(SELECT 'x'
FROM user_tables b
WHERE b.table_name
= SUBSTR(a.table_name,1,26) || '$AUD');
--Select table def of audit table, sans audit columns.
CURSOR cur_col2audit(p_audittbl USER_TABLES.TABLE_NAME%TYPE) IS
SELECT column_name
FROM user_tab_columns
WHERE table_name = p_audittbl
AND column_name NOT IN
('AUD_ACTION','AUD_TIMESTAMP','AUD_USER')
ORDER BY column_id;
BEGIN
FOR cur_tbl2audit_rec IN cur_tbl2audit LOOP
DBMS_OUTPUT.PUT_LINE('CREATE OR REPLACE TRIGGER '||
SUBSTR(cur_tbl2audit_rec.table_name,1,23)||'$AUDTRG '||CHR(10)||
' AFTER INSERT OR DELETE OR UPDATE '||
'ON '||cur_tbl2audit_rec.table_name||
' FOR EACH ROW ');
v_prefix := ':new';
v_condition := 'IF INSERTING OR UPDATING THEN';
DBMS_OUTPUT.PUT_LINE('DECLARE '||CHR(10)||
'v_operation VARCHAR2(10) := NULL;');
DBMS_OUTPUT.PUT_LINE('BEGIN ');
IF v_prefix = ':new' THEN
DBMS_OUTPUT.PUT_LINE(
' IF INSERTING THEN '||CHR(10)||
' v_operation := ''INS''; '||CHR(10)||
' ELSIF UPDATING THEN '||CHR(10)||
' v_operation := ''UPD''; '||CHR(10)||
' ELSE '||CHR(10)||
' v_operation := ''DEL''; '||CHR(10)||
' END IF; '||CHR(13));
END IF;
LOOP
DBMS_OUTPUT.PUT_LINE(v_condition||CHR(10));
DBMS_OUTPUT.PUT_LINE(' INSERT INTO '||
SUBSTR(cur_tbl2audit_rec.table_name,1,26) || '$AUD (');
--Loop through 1st to get column names:
FOR cur_col2audit_rec IN cur_col2audit
(cur_tbl2audit_rec.table_name) LOOP
DBMS_OUTPUT.PUT_LINE(cur_col2audit_rec.column_name|| ',');
END LOOP;
DBMS_OUTPUT.PUT_LINE('aud_action,aud_timestamp,aud_user) '||
'VALUES (');
--Loop a 2nd time for the values:
FOR cur_col2audit_rec IN cur_col2audit(
cur_tbl2audit_rec.table_name) LOOP
DBMS_OUTPUT.PUT_LINE(v_prefix||'.'||
cur_col2audit_rec.column_name|| ',');
END LOOP;
DBMS_OUTPUT.PUT_LINE('v_operation,SYSDATE,USER);'||CHR(10));
EXIT WHEN v_prefix = ':old';
v_prefix := ':old';
v_condition := 'ELSE ';
END LOOP;
DBMS_OUTPUT.PUT_LINE(' END IF;'||CHR(10)||
'END;'||CHR(10)||'/'||CHR(10));
END LOOP;
EXCEPTION
--Any additional error checking would go here.
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failure building audit triggers : '||
SUBSTR(SQLERRM,1,200));
RAISE;
END;
/
SPOOL OFF
--Build the audit triggers:
@build_audit_triggers.sql
Next: Making the Trigger Code Readable >>
More Database Articles
More By Michael Kleane
