|
--
-- BUILD_AUD_TRIG.SQL
--
-- Peter Robson, March 2001.
--
set echo off
set sqlprompt ""
set trims on
set feedback off
set pagesize 0
set verify off
set feedback off
undefine table
---
--- Automatic Building of a Table Trigger.
--- =====================================
---
--- Notes: The variable used throughout these scripts is 'table'
----
---- 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.
----
---- There are 8 sql scripts which run. Some are used twice.
----
---- This script prompts for the master table name.
---- It requires the following:
---- Master table must have: user_entered,
---- date_entered
---- user_modify
---- date_modify
---- func
---- The associated history table must exist,
---- and it must be named according to the standard
---- format of 'master_table_CPY', where the master table
---- is named 'Master_table'.
---- The history table must contain the 3 additional audit fields
---- auduser
---- auddate
---- audfunc
----
select ' ' from dual;
accept table prompt 'Enter Table_Name to build Audit trigger on: '
set termout off
spool trig_1.sql
select
'create or replace trigger '||substr('&table',1,26)||'_aud before insert '||chr(10)||
'or delete or update on '||'&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)||
' :n.user_entered :=user;'||chr(10)||
' :n.date_entered :=sysdate;'||chr(10)||
' :n.func :=''I'';'||chr(10)||
'end if;'||chr(10)||
' if updating then '||chr(10)||
' :n.user_modify :=user;'||chr(10)||
' :n.date_modify :=sysdate;'||chr(10)||
' :n.func :=''U'';'||chr(10)||
' insert into '||substr('&table',1,26)||'_cpy '||chr(10)||
' ( '
from dual
/
---
--- Part 2:
---
select column_name,','
from user_tab_columns
where table_name=upper('&table')
order by column_id
/
---
--- Part 3
---
select
' auduser,'||chr(10)||
' auddate,'||chr(10)||
' audfunc '||chr(10)||
' ) values ('
from dual
/
---
--- Part 4
---
select
' :o.'||column_name,','
from user_tab_columns
where table_name=upper('&table')
order by column_id
/
---
--- Part 5
---
select
' user,'||chr(10)||
' sysdate,'||chr(10)||
' ''U'')'||chr(10)||
' ;'||chr(10)||
' end if;'||chr(10)||
'/* '||chr(10)||
'====================='||chr(10)||
' Deleting '||chr(10)||
'====================='||chr(10)||
'*/'||chr(10)||
' if deleting then '||chr(10)||
' insert into '||substr('&table',1,26)||'_cpy'||chr(10)||
' ('
from dual
/
---
--- Part 2
---
select
column_name,','
from user_tab_columns
where table_name=upper('&table')
order by column_id
/
rem--
rem-- Part 3
---
select
'auduser,'||chr(10)||
'auddate,'||chr(10)||
'audfunc '||chr(10)||
') values ('
from dual
/
---
--- Part 6
---
select
' :o.'||column_name,','
from user_tab_columns
where table_name=upper('&table')
and column_name != 'FUNC'
order by column_id
/
select
' ''D'','||chr(10)||
' user,'||chr(10)||
' sysdate,'||chr(10)||
' ''D'')'||chr(10)||
' ;'||chr(10)||
' end if;'||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_1.sql
undefine table
set pagesize 24
set sqlprompt "SQL>"
set echo on
-- |
|
