UPDATE CASCADE PACKAGE
Generates needed package and triggers to support update cascade in Oracle
without removing or infringing on DECLARITIVE RI.
This package supports:
- Tables with multi-part primary keys (primary key(a,c,b))
- Update cascade to many child tables from one parent
- Self-referencing integrity such as that found in the SCOTT.EMP table
(mgr->empno)
- Is completely application transparent. The application does not know it is
happening
- Versions 7.0 and above of the database.
- Tuned and optimized to fully avoid full table scans on all tables (complete
with utility to show you un-indexed foreign keys in a schema, Cascading an
update to un-indexed foreign keys can be bad).
This solution has the
following restrictions:
- All foreign keys to the parent table must point to the primary key
constraint of the parent table. They cannot point to a unique constraint on
the parent table, they must point to the primary key.
- No other unique constraints/indexes may be in place on the parent
table other then the primary key constraint.
- Updates to primary keys that do not generate 'new' primary keys are not
currently supported. For example, take the standard DEPT table. The update
statement
"update dept set deptno = deptno+10" will not work
whereas the update "update dept set deptno = deptno+1" will. The
first update will change 10->20, 20->30 and so on. Problem is that
10->20 is not generating a 'new' primary key. On the other hand,
deptno=deptno+1 does not have this problem since 10->11, 20->21 and so on.
NOTE: an update that affects a single row will never suffer from this
problem.
- the owner of the parent table must also be the owner of the child tables.
- the owner of the parent table must run the following package in their
schema. This package must be installed for each user that wants to generate
update cascade support. It may be dropped after the cascade support has been
generated.
- the owner of the parent table must have been granted create procedure and
create trigger. these priveleges may not be inherited from a role.
Installing, Using, and seeing how the demo works
This package consists of four SQL scripts
- uc.sql
- demobld.sql
- unindex.sql
- generate.sql
UC.SQL
UC.SQL should be run by any user wanting to implement update
cascade. It will create
- A package spec for update_cascade
- A package body for update_cascade
Once this package is installed,
you are able to implement update cascade on any table via the pl/sql call:
1 | PROCEDURE update_cascade.on_table |
2 | Argument Name Type In/Out Default? |
4 | P_TABLE_NAME VARCHAR2 IN |
5 | P_PRESERVE_ROWID BOOLEAN IN DEFAULT |
6 | P_USE_DBMS_OUTPUT BOOLEAN IN DEFAULT |
| Input Name |
Default |
Usage |
| p-table-name |
NO DEFAULT |
is the name of the parent table |
| p-preserve-rowid |
TRUE |
affects the generation of the code used to implement the generated packages.
If set to TRUE (the default) the rowid of the updated parent row will not change
due to the update. If set to FALSE, the rowid of the updated row WILL change BUT
the code executes in about 66% of the time. |
| p-use-dbms-output |
FALSE |
defaults to FALSE which means the update_cascade package will execute
(create) the packages/triggers. If set to true, the generated code will be
printed using dbms_output.put_line (make sure to set serveroutput on size 100000
before using TRUE if you want to see the code).
|
For example....
01 | SQL> REM will do the default cascade support on |
02 | SQL> REM the dept table |
03 | SQL> REM ROWIDS will be preserved (unchanged). |
04 | SQL> REM Package will be created in the database |
05 | SQL> <b>exec update_cascade.on_table( 'dept' )</b> |
06 | SQL> REM will do the same as above but will not |
07 | SQL> REM preserve the rowids of the parent table |
08 | SQL> REM (the rowids will change). This version |
09 | SQL> REM runs in about 2/3 of the time of the first |
11 | SQL> <b>exec update_cascade.on_table( 'dept', false )</b> |
12 | SQL> REM will do the same as above but will not |
13 | SQL> REM preserve the rowids of the parent table |
14 | SQL> REM (the rowids will change). This version |
15 | SQL> REM runs in about 2/3 of the time of the first |
16 | SQL> REM one. Also, the packages will be written |
17 | SQL> REM to the SCREEN, not into the database. |
18 | SQL> <b>exec update_cascade.on_table( 'dept', false, true )</b> |
The above would generate and compile all of the packages/triggers need to
support cascading updates on the dept table to any other table. You would run
this any time you
- changed the primary key definition of the dept table
- added a child table to the schema (eg: executed a create table and that
table points to dept)
- removed a child table from schema
NOTE: The user executing
update_cascade must have been granted
- CREATE TRIGGER
- CREATE PROCEDURE
directly to themselves. They cannot just have the
privilege via a role.
The other mode of calling update_cascade.on_table is as follows:
3 | SQL> exec update_cascade.on_table( p_table_name => 'dept', p_use_dbms_output =><b>TRUE</b> ) |
The above would generate and
print (as opposed to compile) all of
the packages/triggers/views needed to support cascading update on the dept table
to any other table. You would use this mode to
- Avoid having to grant CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE directly
to the person (they can get these priveleges via a role like DBA).
- To inspect the generated code to understand what it does.
- To generate a package that can be used to install update cascade support at
another site without giving them the update_cascade package itself.
The other mode of calling update_cascade.on_table is as follows:
1 | SQL> exec update_cascade.on_table( p_table_name => 'dept', p_preserve_rowid =><b>FALSE</b> ) |
The above would generate
faster versions of the udpate
cascade packages. They run in 2/3 of the time of the default version but the
rowid's of the updated parent records will change.
This makes this version
less desirable with FORMS. If you use forms heavily, use the default mode so
that rowids are preserved. The triggers to cascade can get away with a lot less
work in this mode. The triggers fire half the time they would in the default
mode and an update that would normally fire and affect 2x the number of rows is
not needed. The generated package code is streamlined as well (less actuall code
is generated, procedural code not just updates).
DEMOBLD.SQL
This script creates one user and three tables in that users
schema. The user is called UCDEMO.
WARNING: This script does a "drop user
ucdemo cascade". It is run as SYSTEM. Please review it before running it. Modify
if you want. Once the script creates the user it will create six tables and
populate them. The tables are:
- DEPT with a primary key
- EMP with a primar key, DECLARATIVE RI to DEPT, DECLARATIVE RI to EMP
- PROJECTS with a primary key, DECLARATIVE RI to EMP
- T1 with a three part primary key
- T2 with a three part primary key and a three part foreign key to T1
- T3 with a three part primary key and a three part foreign key to T2
To begin the demo, you might issue:
1 | SQL> update dept set deptno=deptno+1; |
2 | update dept set deptno=deptno+1 |
5 | ORA-02292: integrity constraint (UCDEMO.SYS_C005184) violated - child record |
Then to see the update cascade in action, you would:
10 | SQL> <b>exec update_cascade.on_table('dept');</b> |
12 | PL/SQL procedure successfully completed. |
15 | SQL> select * from dept; |
19 | 10 ACCOUNTING NEW YORK |
24 | SQL> select empno, deptno from emp where deptno = 10; |
32 | SQL> update dept set deptno = <b>deptno+1;</b> |
36 | SQL> select * from dept; |
40 | <b>11</b> ACCOUNTING NEW YORK |
41 | <b>21</b> RESEARCH DALLAS |
42 | <b>31</b> SALES CHICAGO |
43 | <b>41</b> OPERATIONS BOSTON |
45 | SQL> select empno, deptno from emp where deptno in ( 10, 11 ); |
As you can see, after the package is installed, the updates cascade
successfully to the child tables. The effect of this is even more dramatic when
you do it to the emp table. The MGR column of the EMP table points to the EMPNO
column of the EMP table. In addition, the EMPNO column of the PROJECTS table
points to the EMPNO column of the EMP table. The following scenario is a good
demo of more complex integrity:
01 | SQL> select empno, mgr from emp; |
22 | SQL> update emp set empno = <b>8000</b> where empno = <b>7698;</b> |
23 | update emp set empno = 8000 where empno = 7698 |
26 | ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record |
30 | SQL> exec update_cascade.on_table('emp') |
32 | PL/SQL procedure successfully completed. |
34 | SQL> update emp set empno = <b>8000</b> where empno = <b>7698;</b> |
38 | SQL> select empno, mgr from emp; |
UNINDEX.SQL
The lack of an index on a foreign key will adversely impact
the performance of a cascading update. For example, the emp table is created
via:
2 | (EMPNO NUMBER(4) primary key, |
5 | MGR NUMBER(4) references emp, |
9 | DEPTNO NUMBER(2) references dept); |
The MGR column and the DEPTNO column are not indexed by default. So, for
example, if one were to issue:
04 | STAT TABLE_NAME COLUMNS COLUMNS |
08 | ok PROJECTS EMPNO EMPNO, PROJ_NO |
The four **** indicate that MGR is a foreign key in the EMP table that is
not indexed. It should be (also see chapter 6 in the server application
developers guide for other reasons why un-indexed foreign keys are bad).
GENERATE.SQL
This is a simple script that will generate all of the
needed update_cascade.on_table( 'table_name' ) statements that need to be
executed for all parent tables in a schema. For example, in the demo account it
would create a spool file that contains:
1 | SQL> @src="" update_cascade="" generate="" dept="" emp="" t1="" prompt="" update="" cascade="" on="" table:="" execute="" update_cascade.on_table(="" t2="" )=""> |
HOW it works:
When you update the primary key of a parent table, you might want to cascade the
update to the children. This is hard to do for many reasons and can be
problematic. This package works around the lack of an update cascade option.
This package uses three triggers to perform it magic.
- A before update trigger; used to reset some package variables
- A before update, for each row trigger; used to capture the before and after
images of the primary keys in pl/sql tables. It also 'undoes' the update to the
primary key.
- An After update trigger that does the following steps:
- 'Clones' the parent records with their new primary key, eg: insert into
parent select NEW_KEY, other_cols from parent where CURRENT_KEY = ( SELECT
OLD_KEY FROM DUAL) for example, given "update dept set deptno=deptno+1", this
would insert the values of 11, 21, 31, 41 into the dept table. 11 would have the
values in the rest of the columns that 10 had. 21 would look like 20 and so on.
- If p_preserve_rowids = TRUE, then the primary keys of the row that was
cloned and the clone would be flip flopped. For example, if you issue: update
dept set deptno = 11 where deptno = 10 we would make 10 become the new value 11
and 11 become the old value 10.
- Re-Parents the child records in all subordinate tables. Performs the
equivalent of: update child set fkey = ( select new_key from DUAL ) where fkey =
( select old_key from DUAL )
- It then removes the 'cloned' parent records or the record with the old
primary key value.
A look at the code
The follow annotated code is the generated packages
and triggers you would create by generating support for the dept table.
The following package spec is what would be generated for the typical 'DEPT'
table found in the scott schema (when declaritive RI is used). See the
annotations in the spec for a description of what each entry means and how it is
used. The annotations are not found in the generated code, the generated code is
not commented.
This generated code preserves rowids. The code that preserves rowids will be
in bold. This code would not be present in the generated package if rowid
preservation was disabled.
1 | SQL> exec update_cascade.on_table('dept',true,true); |
1 | create or replace package "uDEPTp" |
1 | rowCnt number default 0; |
2 | <b>inTrigger boolean default FALSE;</b> |
1 | type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer; |
3 | p_old_C1 in "DEPT"."DEPTNO"%type |
4 | ,p_new_C1 in out "DEPT"."DEPTNO"%type |
This is the package body generated. It implements the above specification
1 | create or replace package body "uDEPTp" |
01 | <b>if ( inTrigger ) then return; end if;</b> |
10 | p_old_C1 in "DEPT"."DEPTNO"%type |
11 | ,p_new_C1 in out "DEPT"."DEPTNO"%type |
16 | <b>if ( inTrigger ) then return; end if;</b> |
05 | old_C1( rowCnt ) := p_old_C1; |
06 | new_C1( rowCnt ) := p_new_C1; |
15 | <b>if ( inTrigger ) then return; end if; |
16 | inTrigger := TRUE;</b> |
1 | for i in 1 .. rowCnt loop |
03 | ,"DNAME","LOC") select |
04 | decode( "DEPTNO", old_c1(i), new_c1(i), old_c1(i) ) |
07 | <b>inTrigger := FALSE;</b> |
11 | <b>inTrigger := FALSE;</b> |
Lastly, we have the three triggers placed on the parent table to effect the
update cascade. The first trigger simply 'resets' the package variables above.
1 | create or replace trigger "uc$DEPT_bu" |
5 | begin "uc$DEPT_pkg".reset; end; |
01 | create or replace trigger "uc$DEPT_bufer" |
07 | "uc$DEPT_pkg".add_entry( |
1 | create or replace trigger "uc$DEPT_au" |
5 | begin "uc$DEPT_pkg".do_cascade; end; |
all.tar
