www .Just SQL. com  

Script Name :

Description :

a site for the SQL*Plus specialist

Site Map


A simple script to prevent any Primary Key field from being modified.

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

-- 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
'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
 '   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)||
 ''' 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;
'  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

[Home] [Inspiration] [About this Site] [Scripts Intro] [Publications]

e-Mail Connection