Thursday, October 08, 2009

http://asktom.oracle.com/tkyte/update_cascade



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:


  1. 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.

  2. No other unique constraints/indexes may be in place on the parent
    table other then the primary key constraint.

  3. 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.

  4. the owner of the parent table must also be the owner of the child
    tables.

  5. 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.

  6. 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:


PROCEDURE update_cascade.on_table
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_TABLE_NAME VARCHAR2 IN
P_PRESERVE_ROWID BOOLEAN IN DEFAULT
P_USE_DBMS_OUTPUT BOOLEAN IN DEFAULT




















Input NameDefaultUsage
p-table-name NO DEFAULTis 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-outputFALSEdefaults 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....

SQL> REM will do the default cascade support on
SQL> REM the dept table
SQL> REM ROWIDS will be preserved (unchanged).
SQL> REM Package will be created in the database
SQL> exec update_cascade.on_table( 'dept' )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change). This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one
SQL> exec update_cascade.on_table( 'dept', false )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table
SQL> REM (the rowids will change). This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one. Also, the packages will be written
SQL> REM to the SCREEN, not into the database.
SQL> exec update_cascade.on_table( 'dept', false, true )



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:


SQL> set feedback off
SQL> spool tmp.sql
SQL> exec update_cascade.on_table( p_table_name => 'dept', p_use_dbms_output =>TRUE )
SQL> spool off


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:


SQL> exec update_cascade.on_table( p_table_name => 'dept', p_preserve_rowid =>FALSE )



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:


SQL> update dept set deptno=deptno+1;
update dept set deptno=deptno+1
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005184) violated - child record
found



Then to see the update cascade in action, you would:


SQL> @uc


Table created.

Table altered.

Package created.

Package body created.

SQL> exec update_cascade.on_table('dept');

PL/SQL procedure successfully completed.

SQL>

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select empno, deptno from emp where deptno = 10;

EMPNO DEPTNO
---------- ----------
7839 10
7782 10
7934 10

SQL> update dept set deptno = deptno+1;


4 rows updated.

SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
11 ACCOUNTING NEW YORK
21 RESEARCH DALLAS
31 SALES CHICAGO
41 OPERATIONS BOSTON

SQL> select empno, deptno from emp where deptno in ( 10, 11 );

EMPNO DEPTNO
---------- ----------
7839 11

7782 11
7934 11

SQL>



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:



SQL> select empno, mgr from emp;

EMPNO MGR
---------- ----------
7839
7698 7839
7782 7839
7566 7839
7499 7698
7521 7698
7654 7698
7902 7566
7369 7902
7788 7566
7844 7698
7876 7788
7900 7698
7934 7782

14 rows selected.

SQL> update emp set empno = 8000 where empno = 7698;

update emp set empno = 8000 where empno = 7698
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found


SQL> exec update_cascade.on_table('emp')

PL/SQL procedure successfully completed.

SQL> update emp set empno = 8000 where empno = 7698;

1 row updated.

SQL> select empno, mgr from emp;

EMPNO MGR
---------- ----------
7839
7782 7839
7566 7839
7499 8000
7521 8000

7654 8000
7902 7566
7369 7902
7788 7566
7844 8000
7876 7788
7900 8000
7934 7782
8000 7839

14 rows selected.




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:


CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4) references emp,
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
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:


SQL> update emp set empno = empno+1;


This would eventually issue:


update emp set mgr = SOME_NEW_VALUE
where mgr = SOME_OLD_VALUE;


Since the MGR field is not indexed, this update would do a full scan. It
would do this full scan once for each row in EMP that was updated.

Unindex.sql will generate output such as:


SQL> @unindex.sql

STAT TABLE_NAME COLUMNS COLUMNS
---- ------------------------------ -------------------- --------------------
**** EMP MGR
ok EMP DEPTNO DEPTNO
ok PROJECTS EMPNO EMPNO, PROJ_NO
ok T2 A A, B
ok T3 A, B A, B, C

SQL>


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:


SQL> @src/update_cascade/generate

prompt Update Cascade on table: DEPT
execute update_cascade.on_table( 'DEPT' )

prompt Update Cascade on table: EMP
execute update_cascade.on_table( 'EMP' )

prompt Update Cascade on table: T1
execute update_cascade.on_table( 'T1' )

prompt Update Cascade on table: T2
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.






SQL> exec update_cascade.on_table('dept',true,true);



The following is a typical package specification generated for a table.
The package spec name is always u || TABLE_NAME || p. The package name is
in mixed case (to prevent collisions with other user objects).


create or replace package "uDEPTp"
as
--

Rowcnt is used to collect the number of rows processed by a given update
statement. It is reset in the uDEPTp.reset routine in a before update
trigger. The 'inTrigger' variable is used to prevent recursive firing of
triggers when p_preserve_rowid = TRUE;

rowCnt number default 0;
inTrigger boolean default FALSE;

--

For each element in the primary key, a table type will be declared and then
an array of that type will be declared to 1.) hold the before image, 2.) the
after image, and 3.) an empty array used to zero out the previous two
arrays.

type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer;
--
empty_C1 C1_type;
old_C1 C1_type;
new_C1 C1_type;
--
--

Reset is the routine fired by the BEFORE UPDATE trigger that resets the
rowcnt variable and empties out the arrays from the previous invocation.

procedure reset;
--

Do cascade is the work horse routine. It performs the actual cascade when
fired from an AFTER UPDATE trigger.

procedure do_cascade;
--


Add Entry simply increments the rowcnt and collects the before/after images
of the primary keys. It also 'undoes' the update to the primary key by
accessing the :new and :old variables.

procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
);
--
end "uDEPTp";
/


This is the package body generated. It implements the above specification

create or replace package body "uDEPTp"
as
--
procedure reset
is
begin
--

This line is present in all routines when p_preserve_rowids = TRUE. It
prevents recursive firing of the triggers.

if ( inTrigger ) then return; end if;
--
rowCnt := 0;
old_C1 := empty_C1;
new_C1 := empty_C1;
end reset;
--
procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
)
is
begin
--
if ( inTrigger ) then return; end if;

--

This code saves the before and after images in pl/sql tables and 'undoes'
the primary key update by setting the new columns back to the old columns.

if (
p_old_C1 <> p_new_C1
) then
rowCnt := rowCnt + 1;
old_C1( rowCnt ) := p_old_C1;
new_C1( rowCnt ) := p_new_C1;
p_new_C1 := p_old_C1;
end if;
end add_entry;
--
procedure do_cascade
is
begin
--
if ( inTrigger ) then return; end if;
inTrigger := TRUE;

--

For every row that was updated we will perform the clone, cascade and
delete....

for i in 1 .. rowCnt loop


This insert clones the parent row, duping the old values with the new
primary key.

insert into DEPT (
"DEPTNO"
,"DNAME","LOC") select
new_C1(i)
,"DNAME","LOC"
from "DEPT" a
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--

This code is generated only when p_preserve_rowids=true and will flip-flop
the old and new primary keys, hence preserving the rowid of the original
parent.

update "DEPT" set
( "DEPTNO" ) =
( select
decode( "DEPTNO", old_c1(i), new_c1(i), old_c1(i) )
from dual )
where ( "DEPTNO" ) =
( select new_C1(i)
from dual )
OR ( "DEPTNO" ) =
( select old_C1(i)
from dual );

--

Do a cascade update to all children tables.

update "EMP" set
( "DEPTNO" ) =
( select new_C1(i)
from dual )
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--


Removing the old primary key value.

delete from "DEPT"
where ( "DEPTNO" ) =
( select old_C1(i)
from dual);
end loop;
--
inTrigger := FALSE;
reset;
exception
when others then
inTrigger := FALSE;
reset;
raise;
end do_cascade;
--
end "uDEPTp";
/






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.


create or replace trigger "uc$DEPT_bu"
before update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".reset; end;



The next trigger, the for each row trigger,
simply calls add_entry for each changed
row.


create or replace trigger "uc$DEPT_bufer"
before update of
"DEPTNO"
on "DEPT"
for each row
begin
"uc$DEPT_pkg".add_entry(
:old."DEPTNO"
,:new."DEPTNO"
);
end;


The last trigger, calls do_cascade to effect the change


create or replace trigger "uc$DEPT_au"
after update of
"DEPTNO"
on "DEPT"
begin "uc$DEPT_pkg".do_cascade; end;







Download




POST A COMMENT

5 Comments:

Blogger Donat said....

Hi Tom,

You might want to update the unindex.sql included in your tar with the better version from your later post

I used your improved query to build one that uses sys_connect_by_path instead of max(decode...). This way, the restriction on the number of constrained columns disappears. :-)

You can read the code here below. I changed the way the data is presented because I like being able to read parent/child information in the results. I also do it on dba_ instead of user_ views to show the information on all schemas. After all, a table could have a foreign key pointing to another user's schema.

Donat
The code:
-- use subquery factoring for performance improvement of the innermost CONNECT BY
WITH my_dba_cons_columns AS
(SELECT owner,
table_name,
constraint_name,
column_name,
position
FROM dba_cons_columns
)
SELECT parent_owner AS "Parent Owner",
parent_table AS "Parent Table",
parent_constraint AS "Parent Constraint",
trim(both ',' FROM PARENT_COLUMNS) AS "Parent Columns",
delete_rule AS "On delete",
child_owner AS "Child Owner",
child_table AS "Child Table",
child_constraint AS "Child Constraint",
trim(both ',' FROM child_columns) AS "Unindexed Child Columns"
FROM
(SELECT owner AS parent_owner,
table_name AS parent_table,
constraint_name AS parent_constraint,
cons_columns AS parent_columns,
connect_by_root delete_rule AS delete_rule,
connect_by_root owner AS child_owner,
connect_by_root table_name AS child_table,
connect_by_root constraint_name AS child_constraint,
connect_by_root cons_columns AS child_columns,
col_cnt
FROM
(-- select only the line with all the columns for each constraint
-- and also count the amount of columns
SELECT DISTINCT owner,
table_name,
constraint_name,
MAX(cons_columns) cons_columns,
COUNT(*) col_cnt
FROM
(-- pivot the column list for each constraint to one line
SELECT owner,
table_name,
constraint_name,
sys_connect_by_path(column_name, ', ') ||',' cons_columns
FROM my_dba_cons_columns
START WITH position = 1
CONNECT BY position = prior position + 1
AND owner = prior owner
AND table_name = prior table_name
AND constraint_name = prior constraint_name
)
GROUP BY owner,
table_name,
constraint_name
)
-- join the list of constraint columns with the constraints
-- and use this join to get referred constraint information
JOIN dba_constraints USING(owner, table_name, constraint_name)
WHERE level = 2
CONNECT BY nocycle constraint_name = prior r_constraint_name
AND owner = prior r_owner
)
WHERE col_cnt > ALL
(SELECT COUNT(*)
FROM dba_ind_columns
WHERE table_owner = child_owner
AND table_name = child_table
AND instr(child_columns, ', '|| column_name||',') > 0
AND column_position <= col_cnt
GROUP BY table_owner,
table_name,
index_name
);

Wed Oct 14, 11:43:00 AM EDT  

Anonymous jipo said....

Hi, tom...thanks a lot..
This works

Now I want to make my table back to normal without update cascade. How?

Thanks

Tue Dec 08, 07:23:00 AM EST  

Blogger Thomas Kyte said....

drop the triggers...

Tue Dec 08, 07:32:00 AM EST  

Blogger Zafer said....

Tom,

thank you for providing the update_cascade package. I have learned a lot simply looking at the code to dynamically generate other packages/triggers.

During testing, I found out that if the table name length (for the primary key table) is > 28 characters, then the update cascade package creates errors at lines
75 and 431. The code in update cascade appends one character at the beginning and one character at the end for the generated package and trigger names. If more than 28,
then the generated object name becomes more than 30, so it issues an error.

Thank you again.

Best regards
Zaf

Fri Dec 31, 04:28:00 PM EST  

Blogger Fodor Istv├ín said....

Hi Tom,
Thanks for the update_cascade package, it helped us tremendously!

I ran into a minor bug though: if the table being updated has virtual columns, the insert in the generated package fails. This is fixed by adding the following change starting from line 251:

replace this:
for x in ( select column_name
from user_tab_columns
where table_name = p_table_name
and column_name not in ...

with
for x in ( select column_name
from user_tab_cols
where table_name = p_table_name
and virtual_column = 'NO'
and column_name not in ...

Cheers

Wed May 09, 10:15:00 AM EDT  

POST A COMMENT

<< Home