Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Enforcing a Cascade Update


Oracle Tips by Burleson
 

What do we need to enforce a cascade update? One method is 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 cause a performance problem. A simpler method is to implement a table-based cascade update. The table would contain the information that 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 Listing 4.3 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) would have to be altered to do an ordered retrieve of the information. Listing 4.3 shows the required CREATE TABLE command.

The table by itself would be of little use. Since the data in the table is dynamic (i.e., multiple tables and columns that would have to be addressed), we must enable our trigger to be able to dynamically reassign these values. The easiest way to do this is to create a set of procedures that utilize the DBMS_SQL Oracle-provided package to dynamically reassign our update variables. Source 4.1 shows the commented code for just such a procedure set, which consists of two procedures: UPDATE_TABLES and UPDATE_COLUMN.

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:

*  main_table holds the name of the table that the update

*  cascades from.

*  table_name is the name(s) of the tables to cascade the

*  update into.

*  column_name is the column in the target table(s) to

*  update

Listing 4.3  Command to create UPDATE_TABLES table.

The Cascade Update Procedures  

By using the DBMS_SQL package to dynamically build the table update command on the fly, we can use the same set of procedures 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 Listing 4.3. If required, the table 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. 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. I have created a sample package for cascade updates; it is shown in Source 4.1.

First create package body

Decided to use package so that all procedures will be in one place and very controllable

M. Ault 1/14/97 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 run time)

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.native);

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,
      ol
d_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 prepopulate
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;

/

Source 4.1  Cascade update package.

The Final Piece: The Trigger

Once the source table and procedures have been built, we need to design a trigger to implement against our master tables and that automatically fires on update to the target master column. The next code section shows an example of this trigger. Notice that the trigger 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 that 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.

This type of combination of table, procedure, and trigger makes it possible to do the cascade update in a controlled, uniform manner. The table storing the update table information is used by multiple tables; thus, the only required operations would involve adding the prerequisite data to the update table and then placing the required trigger on the tables to undergo the cascade update. Triggers are covered in more detail in Chapter 7, Administration of Other Database Objects.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.
   
  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter