 |
|
A
Generic Cascade Update Procedure
Oracle Tips by Mike Ault |
Enforcing a
Cascade Update
--
-- main_table holds the name of the table that the update
-- cascades from.
--
-- table_name holds the name(s) of the tables to cascade the
update
-- into.
--
-- column_name is the name of the column in the target table(s)
to
-- update
Figure 1: Example DDL to create a cascade
update source table.
The table by itself would be of little use.
Since the data in the table is dynamic (i.e. multiple tables and
columns that will need to be addressed) we must enable our trigger
to be able to dynamically re-assign these values. The easiest way to
do this is to create a set of procedures that utilizes the DBMS_SQL
Oracle provided package to dynamically re-assign our update
variables. Figure 2 shows the commented code for just such a
procedure set. The set consists of two procedures, UPDATE_TABLES and
UPDATE_COLUMN.
SEE CODE DEPOT FOR FULL SCRIPTS
 |
For more information on this topic, I recommend Don
Burleson's latest book "Oracle
Tuning: The Definitive Reference".
You can buy it direct from the publisher for 50%-off and get
instant access to the code depot of Oracle tuning scripts: |
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|
