Update Cascade Tips Oracle Tips by Burleson Consulting Mike Ault - August 12th, 2004

Many times we have the situation where a master table may be updated and dependent tables are left without a link back to the master. In this situation a cascade update option would be a nice one to have. Unfortunately Oracle Corp. doesn't provide this capability in a native manner so a PL/SQL option must be developed.

An example of a need for a cascade update would be in the situation where a dependent table could be dependent on one or more tables. For example, there may be two types of customer, one who has bought from us and we have marketing information for and another that is new to us, may buy from us, but may go with another vendor. If there are dependent tables (such as an interaction log that tracks phone calls to and from customers) it would be nice to be able to switch the dependencies from our new customer table to our established customer table.

Enforcing a Cascade Update

What would be needed to enforce a cascade update? One method would be to utilize data dictionary tables and views to backtrack foreign key relations and then apply updates along this path. However, this may be a lengthy process and can be a performance problem. A simpler method would be to implement a table based cascade update. The table would contain the information a procedure would need to update all tables that are dependent upon a main or master table. Therefore, the table would have to contain the master table name, the dependent table(s) and in case we can't duplicate the exact column name across all of the dependent tables, the column to update.

The table DDL script in figure 1 meets these requirements.  If required, a fourth column indicating an update order could be added and the cursor in the UPDATE_TABLES procedure detailed later altered to do an ordered retrieve of the information.

CREATE TABLE update_tables

(

            main_table        VARCHAR2(30) NOT NULL,

            table_name       VARCHAR2(30) NOT NULL,

            column_name    VARCHAR2(30) NOT NULL,

CONSTRAINT pk_update_tables

PRIMARY KEY (main_table,table_name,column_name)

USING INDEX

TABLESPACE tool_indexes)

STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)

TABLESPACE tools

/

-- Column definitions are as follows:

--

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

-- First create package body

-- Decided to use package so that all procedures will

-- be in one place and very controllable

-- M. Ault Rev 1.0

--

CREATE OR REPLACE PACKAGE cascade_update AS

--

-- First package is update_column

-- This package actually does the work

-- using DBMS_SQL to dynamically rebuild the

-- UPDATEs at run time for each table.

--

PROCEDURE update_column(

            old_value           IN VARCHAR2,

            new_value         IN VARCHAR2,

            table_name       IN VARCHAR2,

            update_column IN VARCHAR2

);

--

-- Next procedure is update_tables

-- It is the loop control procedure for

-- the trigger and calls update_column

--

PROCEDURE update_tables(

            source_table      IN VARCHAR2,

            old_value           IN VARCHAR2,

            new_value         IN VARCHAR2

);

--

-- End of PACKAGE HEADER

--

END cascade_update;

/

--

-- Now build package body

-- That actually holds the

-- procedures and code

--

CREATE OR REPLACE PACKAGE BODY cascade_update AS

 

PROCEDURE update_column(

            old_value           IN VARCHAR2,

            new_value         IN VARCHAR2,

            table_name       IN VARCHAR2,

            update_column IN VARCHAR2)

AS

--

-- define state variables for dbms_sql procedures

--

            cur                    INTEGER;

            rows_processed             INTEGER;

--

-- start processing

-- (dbms_output calls are for debugging

-- commented out during normal runtime)

--

BEGIN

--          DBMS_OUTPUT.PUT_LINE('Table name: '||table_name||' Column: '||update_column);

            --

            -- initialize the dynamic cursor location for

            -- the dbms_sql process

            --

            cur:=DBMS_SQL.OPEN_CURSOR;

            --

            -- populate the initialized location with the statement to be

            -- processed

            --

--          DBMS_OUTPUT.PUT_LINE(

--          'UPDATE '||table_name||' set '||update_column||'='||chr(39)||new_value||chr(39)||chr(10)||

--          ' WHERE '||update_column||'='||chr(39)||old_value||chr(39)||' AND 1=1');

            --

            dbms_sql.parse(cur,

            'UPDATE '||table_name||' set '||update_column||'='||chr(39)||new_value||chr(39)||chr(10)||

            ' WHERE '||update_column||'='||chr(39)||old_value||chr(39)||' AND 1=1',dbms_sql.v7);

            --

            -- execute the dynamically parsed statement

            --

            rows_processed:=DBMS_SQL.EXECUTE(cur);

            --

            -- close dynamic cursor to prepare for next table

            --

            DBMS_SQL.CLOSE_CURSOR(cur);

--

-- END PROCEDURE

--

END update_column;

 

PROCEDURE update_tables(

            source_table      IN VARCHAR2,

            old_value           IN VARCHAR2,

            new_value         IN VARCHAR2) as

--

-- Create the cursor to read records

-- from bbs_siteid_tables

-- Use * to prohibit missing a column

--

            CURSOR get_table_name IS

                        SELECT

                                    *

                        FROM

                                    bbs_update_tables

                        WHERE

                                    main_table=source_table;

--

-- Define rowtype variable to hold record from

-- bbs_siteid_tables. Use rowtype to allow for

-- future changes.

--

            update_rec        update_tables%ROWTYPE;

--

-- start processing

--

BEGIN

--

-- open and fetch values with cursor

--

 OPEN get_table_name;

 FETCH get_table_name INTO update_rec;

--

-- now that cursor status is open and values in

-- variables can begin loop

--

LOOP

--

-- using the notfound status we had to pre-populate

-- record

--

   EXIT WHEN get_table_name%NOTFOUND;

--

-- Initiate call to the update_column procedure

--

   update_column(old_value, new_value, update_rec.table_name, update_rec.column_name);

--

-- Now get next record from table

--

   FETCH get_table_name INTO update_rec;

--

-- processing returns to loop statement

--

 END LOOP;

--

-- close cursor and exit

--

 CLOSE get_table_name;

--

-- end of procedure

--

END update_tables;

--

-- end of package body

--

END cascade_update;

/

 

Figure 2: The package containing the procedures for cascade update

 

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.

The Final Piece, the Trigger

Once the source table and procedures are built, we need to design a trigger to implement against our master tables that automatically fires on update to the target master column.

Figure 3 shows an example of this trigger. One thing to notice about the trigger is that it passes the master table name to the UPDATE_TABLES procedure as well as the old and new values for the column being updated. This allows the UPDATE_TABLES procedure to select only the names and columns for the tables which are dependent upon the master table for which the trigger is implemented. This allows multiple master tables to utilize a single source table.

-- The calling trigger has to be of the form:

 

            CREATE OR REPLACE TRIGGER cascade_update_<tabname>

              AFTER UPDATE OF <column> ON <tabname>     

              REFERENCING NEW AS upd OLD AS prev

                FOR EACH ROW

                  BEGIN

                        cascade_update.update_tables('<tabname>',:prev.<column>,:upd.<column>);

                  END;

--

-- Note how the table name is passed to the procedure, this must be done.

 

Figure 3: Example trigger utilizing the cascade update procedures.

 

Summary

Utilizing the procedures and trigger described in this article a DBA can enforce a cascade update against any set of master-dependent tables without the performance hit of searching the data dictionary for the relationship definitions.