Update cascade Tom kyte Example (Programatic)

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 rowbegin"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;