|
--
-- BUILD_LOG_TRIG.sql
--
-- Peter Robson, March 2001.
--
set echo off
--
---
--- Automatic Batch Building of the Audit Trigger.
--- =============================================
--
-- The completed trigger will be named '<table_name_LOG>'. If the table
-- name exceeds 26 characters in length, those trailing characters will be cut.
--
-- This trigger ensures that every time any modification is made
-- to a table for which this trigger build script has been generated,
-- a Log of the current function will be recorded in the table LOG_MODS.
-- Ensure this table exists. It contains the following three fields:
--
-- table_name varchar2(30)
-- aud_flag char(1)
-- aud_date date
--
----
---- It is ASSUMED that the trigger is being generated for a table
---- owned by the user, as frequent reference is made to the
---- Oracle data dictionary table 'user_tab_columns'.
----
---- See how use of '||chr(10)||' forces a new line
---- in the output script. This is important for subsequent
---- readability of the trigger body.
----
set pagesize 0
set verify off
set feedback off
set sqlprompt ''
--
undefine table
--
select ' ' from dual;
select 'Building Audit trigger on Master table:' from dual;
select ' ' from dual;
accept table prompt 'Enter name of Master table: '
--
set termout off
--
spool trig_2.sql
select
'create or replace trigger '||upper(substr('&table',1,26))||'_LOG '||chr(10)||
'after insert or update or delete on '||upper('&table')||chr(10)||
'referencing new as n old as o for each row '||chr(10)||
'begin'||chr(10)||
'--'||chr(10)||
'--========================='||chr(10)||
'-- Insert'||chr(10)||
'--========================='||chr(10)||
'--'||chr(10)||
'if inserting then '||chr(10)||
' insert into log_mods (table_name,aud_flag,aud_date) values ('||chr(10)||
' upper(''&table''),' ||chr(10)||
' ''I'',' ||chr(10)||
' sysdate );' ||chr(10)||
'end if;'||chr(10)||
'--'||chr(10)||
'--========================='||chr(10)||
'-- Update'||chr(10)||
'--========================='||chr(10)||
'--'||chr(10)||
' if updating then '||chr(10)||
' insert into log_mods (table_name,aud_flag,aud_date) values ('||chr(10)||
' upper(''&table''),'||chr(10)||
' ''U'',' ||chr(10)||
' sysdate );' ||chr(10)||
'end if;' ||chr(10)||
'-- ' ||chr(10)||
'--=====================' ||chr(10)||
'-- Deleting control is via ' ||chr(10)||
'-- trigger on _HIST table ' ||chr(10)||
'--=====================' ||chr(10)||
'--'||chr(10)||
' if deleting then ' ||chr(10)||
' insert into log_mods (table_name,aud_flag,aud_date) values ('||chr(10)||
' upper(''&table''),'||chr(10)||
' ''D'',' ||chr(10)||
' sysdate );' ||chr(10)||
'end if;' ||chr(10)||
'--' ||chr(10)||
'end;' ||chr(10)||
'/' ||chr(10)
from dual
;
spool off
set feedback on
set verify on
set heading on
set termout on
start trig_2.sql
set pagesize 24
undefine 1
set sqlprompt "SQL>"
set echo on |
|
