The Cascade
Update Procedures
Use of the DBMS_SQL package to dynamically
build the table update command on the fly allows the same set of
procedures to be used for any set of master-dependent tables that
have entries in the source table.
The UPDATE_TABLES procedure accepts the master
table name, the old value for the column to be updated and the new
value for the column. The procedure uses a standard cursor fetch to
retrieve the dependent table names and dependent table column names
from the source table shown in figure 1. If desired, the table from
figure 1 could be altered to accept an ordering value for each
master-dependent set to allow the cascade update to be done in a
specific order if required. Using this information and the new and
old values for the column from the trigger call, the UPDATE_COLUMN
procedure dynamically rebuilds the table update command to update
the appropriate tables.
