11.4 SQL Scripts to Generate Scripts Creating one or more of the three triggers presented in this chapter on each table you'd like to audit can be very tedious . The following three scripts can be used to generate SQL-generating SQL code to create the before-insert , after-update, and after-delete triggers for any table you would like to audit. By running these scripts, you can automate the process of script creation for all of the tables for which you want to implement auditing. At the beginning of each script, you will find a brief purpose statement and any comments about what the script will not handle (if applicable ).  | The script presented here uses the DUAL table. Heavy use of the DUAL table can lead to serialization in the SGA. You might want to create multiple DUAL tables to spread the load. | | 11.4.1 Generating a Before-Insert Trigger Script The following example generates a before_insert trigger script. REM Purpose: Audit trail before-insert trigger generator. REM This script creates a BEFORE-INSERT trigger script for REM a given table for auditing. SET SPACE 0; SET VERIFY OFF; SET NUMWIDTH 4; SET HEADING OFF; SET LINESIZE 80; SET PAGESIZE 0; SET FEEDBACK OFF; SET RECSEP OFF; SET LONG 255; SET ECHO OFF; SET TRIMSPOOL ON; PROMPT -----------------------------------------------------------; PROMPT Audit Trail BEFORE-INSERT Trigger Creation Script Generator; PROMPT -----------------------------------------------------------; -- accept tabowner char PROMPT 'Table Owner: '; ACCEPT tabname char PROMPT 'Table Name: '; -- accept filename char PROMPT 'Spool to <filename>: '; PROMPT Spool File Name: &&tabname._bi0.trg; PROMPT ------------------------------------------------------; PROMPT Working...; COLUMN remarks FORMAT a80; COLUMN col0 FORMAT 999999990 NOPRINT; COLUMN col1 FORMAT a80; DEFINE spoolfile = &&tabname._bi0.trg SPOOL &&spoolfile; REM -------------------------------------------------------------------- REM This query generates a file header. REM -------------------------------------------------------------------- SELECT RPAD('rem ' '&&spoolfile',80,' ') RPAD('rem ' 'Generated on ' sysdate ' by ' user '.',80,' ') RPAD('rem ' 'Script to create BI audit trigger for the ' UPPER('&&tabname') ' table.',80,' ') RPAD(' ',80,' ') remarks FROM dual; REM -------------------------------------------------------------------- REM This query generates the trigger text. REM -------------------------------------------------------------------- SELECT RPAD('create or replace trigger ' table_name '_BI0',80,' ') RPAD(' before insert',80,' ') RPAD(' on ' lower(table_name),80,' ') RPAD(' for each row',80,' ') RPAD(' when (new.rid is null or new.rid = 0)',80,' ') RPAD(' ',80,' ') RPAD('begin',80,' ') RPAD(' select trunc(' 'seq_rid.nextval)',80,' ') RPAD(' into :new.rid',80,' ') RPAD(' from dual;',80,' ') RPAD('end;',80,' ') '/' col1 FROM user_tables WHERE table_name = UPPER('&&tabname'); SELECT 0 col0, null col1 FROM dual UNION SELECT 1 col0, 'exit;' col1 FROM dual ORDER BY 1; SPOOL OFF; SET SPACE 1; SET VERIFY ON; SET NUMWIDTH 10; SET HEADING ON; SET PAGESIZE 14; SET FEEDBACK ON; 11.4.2 Generating an After-Update Trigger Script The following example generates an after_update trigger script. REM Purpose: Audit Trail After-Update Trigger Generator. REM This script creates a AFTER-UPDATE trigger script for REM a given table for auditing. REM Notes: Does not handle LONG or similar columns at all! REM Chr(39) is the code representation of a single quote sign SET SPACE 0; SET VERIFY OFF; SET NUMWIDTH 4; SET HEADING OFF; SET LINESIZE 80; SET PAGESIZE 0; SET FEEDBACK OFF; SET RECSEP OFF; SET LONG 255; SET ECHO OFF; SET TRIMSPOOL ON; PROMPT ----------------------------------------------------------; PROMPT Audit Trail AFTER-UPDATE Trigger Creation Script Generator; PROMPT ----------------------------------------------------------; ACCEPT tabname char PROMPT 'Table Name: '; PROMPT Spool File Name: &&tabname._au0.trg; PROMPT ----------------------------------------------------------; PROMPT Working...; COLUMN remarks FORMAT a80; COLUMN col0 FORMAT 999999990 noprint; COLUMN col1 FORMAT a80; COLUMN col2 FORMAT a80; COLUMN col3 FORMAT a10; COLUMN col4 FORMAT a80; COLUMN col5 FORMAT a80; COLUMN col6 FORMAT a80; COLUMN col7 FORMAT a80; COLUMN col8 FORMAT a80; SPOOL &&tabname._au0.trg; REM -------------------------------------------------------------------- REM This query generates a file header. REM -------------------------------------------------------------------- SELECT RPAD('-- ' '&&tabname._au0.trg',80,' ') RPAD('-- ' 'Generated on ' SYSDATE' by ' user '.', 80, ' ') RPAD('-- ' 'Script to create AFTER-UPDATE audit trigger ' 'for the ' UPPER('&&tabname') ' table.',80,' ') RPAD(' ',80,' ') remarks FROM dual; REM -------------------------------------------------------------------- REM These queries generate the trigger text. REM -------------------------------------------------------------------- SELECT RPAD('create or replace trigger ' table_name '_AU0', 80, ' ') RPAD(' after update of', 80, ' ') col1 FROM user_tables WHERE table_name = UPPER('&&tabname'); SELECT ' ' LOWER(utc.column_name) ',' col1 FROM user_tab_columns utc WHERE utc.table_name = UPPER('&&tabname') AND tc.data_type in ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER') AND utc.column_name not in ('RID', 'CREATOR', 'TIMESTAMP') ORDER BY utc.column_id; SELECT ' ' LOWER(utc.column_name) col1 FROM user_tab_columns utc WHERE utc.table_name = UPPER('&&tabname') AND utc.column_name = 'RID'; SELECT RPAD(' on ' LOWER(table_name),80,' ') RPAD(' for each row',80,' ') RPAD(' ',80,' ') RPAD('declare',80,' ') RPAD(' raid number(10);',80,' ') RPAD(' ',80,' ') RPAD('begin',80,' ') RPAD(' select ' 'seq_audit.nextval into raid from dual;',80,' ') RPAD(' ',80,' ') RPAD(' audit_trail.record_transaction',80,' ') RPAD(' (raid, ' CHR(39) table_name CHR(39) ', :' 'old.rid, ' CHR(39) 'U' CHR(39) ');',80,' ') RPAD(' ',80,' ') col1 FROM user_tables WHERE table_name = upper('&&tabname'); REM -------------------------------------------------------------------- REM This section builds the column comparison section. REM -------------------------------------------------------------------- SELECT RPAD(' if nvl(:old.' LOWER(a.column_name) ',' DECODE(a.data_type, 'NUMBER','0', 'DATE', CHR(39) '31-dec-9595' CHR(39), CHR(39) ' ' CHR(39) ) ') != ',80,' ') col4, RPAD(' NVL(:new.'LOWER(a.column_name)',' DECODE(a.data_type, 'NUMBER','0', 'DATE', CHR(39) '31-dec-9595' CHR(39), CHR(39) ' ' CHR(39) ) ') then',80,' ') col5, RPAD(' audit_trail.column_update',80,' ') col6, RPAD(' (raid, ' chr(39) a.column_name CHR(39) ',',80,' ') col7, RPAD(' :old.' LOWER(a.column_name) ', :new.' lower(a.column_name) ');',80,' ') col8, ' end if;' col3 FROM user_tab_columns a WHERE a.table_name = upper('&&tabname') AND a.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER') AND a.column_name NOT IN ('CREATOR', 'TIMESTAMP') ORDER BY a.column_id; REM -------------------------------------------------------------------- REM This section finishes up the trigger. REM -------------------------------------------------------------------- SELECT null FROM dual; SELECT 0 col0, 'end;' col1 FROM dual UNION SELECT 1 col0, '/' col1 FROM dual ORDER BY 1; -- Optional: If you want "exit" at the end of your script. -- SELECT 0 col0, -- null col1 -- FROM dual -- UNION -- SELECT 1 col0, -- 'exit;' col1 -- FROM dual -- ORDER BY 1; SPOOL OFF; SET SPACE 1; SET VERIFY ON; SET NUMWIDTH 10; SET HEADING ON; SET PAGESIZE 14; SET FEEDBACK ON; 11.4.3 Generating an After-Delete Trigger Script The following example generates an after_delete trigger script. REM Purpose: Audit Trail After Delete Trigger Generator. REM This script creates a AFTER-DELETE trigger script for REM a given table for auditing. REM Notes: Does not handle LONG or similar columns at all! SET SPACE 0; SET VERIFY OFF; SET NUMWIDTH 4; SET HEADING OFF; SET LINESIZE 80; SET PAGESIZE 0; SET FEEDBACK OFF; SET RECSEP OFF; SET LONG 255; SET ECHO OFF; SET TRIMSPOOL ON; PROMPT ----------------------------------------------------------; PROMPT Audit Trail AFTER-DELETE Trigger Creation Script Generator; PROMPT ----------------------------------------------------------; ACCEPT tabname char PROMPT 'Table Name: '; PROMPT Spool File Name: &&tabname._ad0.trg; PROMPT ------------------------------------------------------; PROMPT Working...; COLUMN remarks FORMAT a80; COLUMN col0 FORMAT 999999990 noprint; COLUMN col1 FORMAT a80; COLUMN col2 FORMAT a80; COLUMN col3 FORMAT a80; SPOOL &&tabname._ad0.trg; REM -------------------------------------------------------------------- REM This query generates a file header. REM -------------------------------------------------------------------- SELECT RPAD('-- ' '&&tabname._ad0.trg',80,' ') RPAD('-- ' 'Generated on ' sysdate ' by ' user '.', 80, ' ') RPAD('-- ' 'Script to create AFTER-DELETE audit trigger ' 'for the ' UPPER('&&tabname') ' table.',80,' ') RPAD(' ',80,' ') remarks FROM dual; REM -------------------------------------------------------------------- REM These queries generate the trigger text. REM -------------------------------------------------------------------- SELECT RPAD('create or replace trigger ' table_name '_AD0',80,' ') RPAD(' after delete',80,' ') RPAD(' on ' lower(table_name),80,' ') RPAD(' for each row',80,' ') RPAD(' ',80,' ') RPAD('declare',80,' ') RPAD(' raid number(10);',80,' ') RPAD(' deleted_data varchar2(2000);',80,' ') RPAD(' ',80,' ') RPAD('begin',80,' ') RPAD(' select seq_audit.nextval into raid from dual;',80,' ') RPAD(' ',80,' ') RPAD(' audit_trail.record_transaction',80,' ') RPAD(' (raid, ' chr(39) table_name chr(39) ', :old.rid, ' chr(39) 'D' chr(39) ');',80,' ') RPAD(' ',80,' ') RPAD(' deleted_data :=',80,' ') col1 FROM user_tables WHERE table_name = upper('&&tabname'); SELECT column_id col0, RPAD(' ' DECODE(a.data_type, 'NUMBER','to_char(', 'DATE','to_char(', null) ':old.' LOWER(a.column_name) DECODE(a.data_type, 'NUMBER',')', 'DATE',')', null) ' ' CHR(124) CHR(124) ' ' CHR(39) CHR(124) CHR(39) ' ' CHR(124) CHR(124), 80, ' ') col1 FROM user_tab_columns a WHERE a.table_name = UPPER('&&tabname') AND a.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER') AND column_id < (SELECT MAX(a2.column_id) FROM user_tab_columns a2 WHERE a2.table_name = UPPER('&&tabname') AND a2.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER')) UNION SELECT column_id col0, RPAD(' ' DECODE(a.data_type, 'NUMBER','to_char(', 'DATE','to_char(', null) ':old.' LOWER(a.column_name) DECODE(a.data_type, 'NUMBER',')', 'DATE',')', null) ';', 80, ' ') col1 FROM user_tab_columns a WHERE a.table_name = UPPER('&&tabname') AND a.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER') AND column_id = (SELECT MAX(a2.column_id) FROM user_tab_columns a2 WHERE a2.table_name = UPPER('&&tabname') AND a2.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2', 'DATE', 'NUMBER')) ORDER BY 1; REM -------------------------------------------------------------------- REM This section finishes up the trigger. REM -------------------------------------------------------------------- SELECT null FROM dual; SELECT RPAD('insert into ' 'audit_delete',80,' ') RPAD('(raid, row_data) values (raid, deleted_data);',80,' ') col1 FROM dual; SELECT 0 col0, 'end;' col1 FROM dual UNION SELECT 1 col0, '/' col1 FROM dual ORDER BY 1; -- Optional - if you want 'exit' at the end of your script. -- SELECT 0 col0, -- null col1 -- FROM dual -- UNION -- SELECT 1 col0, -- 'exit;' col1 -- FROM dual -- ORDER BY 1; SPOOL OFF; SET SPACE 1; SET VERIFY ON; SET NUMWIDTH 10; SET HEADING ON; SET PAGESIZE 14; SET FEEDBACK ON; |
