This script was built in a hurry. The data replication system that I have also built depends crucially on the integrity on the Primary Keys (PK) of the participating tables.
It is my contention that a relational database management system should, by default, both insist on the definition of a PK during the 'create table' phase, and furthermore, should never permit any subsequent modification of a PK field. Unfortunetaly, Oracle leaves it to the users to explicitly set up both these conditions themselves, if they so wish --
--
-- BUILD_STOP_PK_TRIG.SQL
--
-- Peter Robson, June 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 to STOP any PK field being Updated.
--- ========================================================================
---
--- Notes: The variable used throughout these scripts is 'table'.
--- You may alternatively replace this with '1', comment out the 'prompt'
--- line, then pass the name of the table against which to build the trigger
--- by submitting the script with the table_name ar arguement.
----
---- 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.
----
---- Note - the final code is NOT optimised. A test block exists for
---- every field in the PK. Efficiency can be obtained by
---- including the equivalence tests for new/old fields into
---- just one test. I built this for speed - refinements may come later!
----
--------------------------------------------------------------------------------------
--
select ' ' from dual;
accept table prompt 'Enter Table_Name to build Audit trigger on: '
set termout off
spool trig_2.sql
select
'create or replace trigger '||substr('&&table',1,26)||'_pkx '||chr(10)||
'before update on '||'&&table' ||chr(10)||
'referencing new as n old as o for each row ' ||chr(10)||
'begin' ||chr(10)||
' if updating then ' ||chr(10)||
'-- ' ||chr(10)||
'-- Trigger to protect pk fields from update' ||chr(10)||
'--' ||chr(10)
from dual
/
---
select
' if ' ||chr(10)||
' :n.'||column_name||' <> :o.'||column_name||' then ' ||chr(10)||
' raise_application_error(-20501,' ||chr(10)||
'chr(10)||'' Column '||column_name||' is a Primary Key field!'' ||chr(10)||' ||chr(10)||
''' YOU MUST NOT UPDATE THE PRIMARY KEY FIELDS FOR THIS TABLE!''||chr(10)||' ||chr(10)||
''' If the primary key is wrong, then insert the details as a'' ||chr(10)||' ||chr(10)||
''' new row with the correct primary key, and then delete the old row.'');' ||chr(10)||
' end if; ' ||chr(10)
from user_cons_columns ucc, user_constraints uc
where ucc.table_name=uc.table_name
and constraint_type='P'
and ucc.table_name = upper('&&table')
order by position;
--
select
' 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_2.sql
undefine table
set pagesize 24
set sqlprompt "SQL>"
set echo on
--
-- end
--
|
