Oracle Package - Update Cascade

Update Cascade Package.sql

DemoBld Package.sql

Generate update_cascade.on_table statements.sql

Report on Unindexed foreign keys.sql

Oracle에서는 MS-SQL과 달리, UPDATE CASCADE를 지원하지 않는다.

그러므로 이를 Trigger나 Package등으로 설정을 하여야 하는데

이를 간단하게 사용할 수 있는 Package가 있다.

 

 

PL/SQL로 다음의 Package를 생성한다.

 

CREATE OR REPLACE PACKAGE UPDATE_CASCADE
AS

    PROCEDURE ON_TABLE( P_TABLE_NAME      IN VARCHAR2,
                        P_PRESERVE_ROWID  IN BOOLEAN DEFAULT TRUE,
                        P_USE_DBMS_OUTPUT IN BOOLEAN DEFAULT FALSE );

END UPDATE_CASCADE;
/

CREATE OR REPLACE PACKAGE BODY UPDATE_CASCADE
AS

TYPE CNAMEARRAY IS TABLE OF USER_CONS_COLUMNS.COLUMN_NAME%TYPE
                   INDEX BY BINARY_INTEGER;

SQL_STMT           VARCHAR2(32000);
USE_DBMS_OUTPUT    BOOLEAN DEFAULT FALSE;
PRESERVE_ROWID     BOOLEAN DEFAULT TRUE;

FUNCTION Q( S IN VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
    RETURN '"'||S||'"';
END Q;

FUNCTION PKG_NAME( S IN VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
    RETURN Q( 'U' || S || 'P' );
END PKG_NAME;

FUNCTION VIEW_NAME( S IN VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
    RETURN Q( 'U' || S || 'V' );
END VIEW_NAME;

FUNCTION TRIGGER_NAME( S IN VARCHAR2, S2 IN VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
    RETURN Q( 'U' || S || S2 );
END TRIGGER_NAME;

FUNCTION STRIP( S IN VARCHAR2 ) RETURN VARCHAR2
IS
BEGIN
    RETURN LTRIM(RTRIM(S));
END STRIP;

PROCEDURE ADD( S IN VARCHAR2 )
IS
BEGIN
    IF ( USE_DBMS_OUTPUT ) THEN
        DBMS_OUTPUT.PUT_LINE( CHR(9) || S );
    ELSE
        SQL_STMT := SQL_STMT || CHR(10) || S;
    END IF;
END ADD;

 

PROCEDURE EXECUTE_IMMEDIATE
AS
    EXEC_CURSOR     INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
    ROWS_PROCESSED  NUMBER  DEFAULT 0;
BEGIN
    IF ( USE_DBMS_OUTPUT ) THEN
        DBMS_OUTPUT.PUT_LINE( '/' );
    ELSE
        DBMS_SQL.PARSE(EXEC_CURSOR, SQL_STMT, DBMS_SQL.NATIVE );
        ROWS_PROCESSED := DBMS_SQL.EXECUTE(EXEC_CURSOR);
        DBMS_SQL.CLOSE_CURSOR( EXEC_CURSOR );
        DBMS_OUTPUT.PUT_LINE(
        SUBSTR( SQL_STMT, 2, INSTR( SQL_STMT,CHR(10),2)-2 ) );
        SQL_STMT := NULL;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_OPEN(EXEC_CURSOR) THEN
        DBMS_SQL.CLOSE_CURSOR(EXEC_CURSOR);
      END IF;
      RAISE;
END;

PROCEDURE GET_PKEY_NAMES
( P_TABLE_NAME      IN OUT USER_CONSTRAINTS.TABLE_NAME%TYPE,
  P_PKEY_NAMES         OUT CNAMEARRAY,
  P_CONSTRAINT_NAME IN OUT USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE )
IS
BEGIN
    SELECT TABLE_NAME, CONSTRAINT_NAME
      INTO P_TABLE_NAME, P_CONSTRAINT_NAME
      FROM USER_CONSTRAINTS
     WHERE ( TABLE_NAME = P_TABLE_NAME
             OR TABLE_NAME = UPPER(P_TABLE_NAME) )
            AND CONSTRAINT_TYPE = 'P' ;

    FOR X IN ( SELECT COLUMN_NAME , POSITION
                 FROM USER_CONS_COLUMNS
                WHERE CONSTRAINT_NAME = P_CONSTRAINT_NAME
                ORDER BY POSITION ) LOOP
        P_PKEY_NAMES( X.POSITION ) := X.COLUMN_NAME;
    END LOOP;
END GET_PKEY_NAMES;

PROCEDURE WRITE_SPEC( P_TABLE_NAME IN USER_CONSTRAINTS.TABLE_NAME%TYPE,
                      P_PKEY_NAMES IN CNAMEARRAY )
IS
    L_COMMA     CHAR(1) DEFAULT ' ';
BEGIN
    ADD( 'CREATE OR REPLACE PACKAGE ' || PKG_NAME(P_TABLE_NAME) );
    ADD( 'AS' );
    ADD( '--' );
    ADD( '    ROWCNT    NUMBER DEFAULT 0;' );
    ADD( '    INTRIGGER BOOLEAN DEFAULT FALSE;' );
    ADD( '--' );

    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '    TYPE C' || STRIP(I) || '_TYPE IS TABLE OF ' ||
                    Q(P_TABLE_NAME) || '.' || Q(P_PKEY_NAMES(I)) ||
                 '%TYPE INDEX BY BINARY_INTEGER;' );
            ADD( '--' );
            ADD( '    EMPTY_C' || STRIP(I) || ' C' || STRIP(I) || '_TYPE;' );
            ADD( '    OLD_C' || STRIP(I) || '   C' || STRIP(I) || '_TYPE;' );
            ADD( '    NEW_C' || STRIP(I) || '   C' || STRIP(I) || '_TYPE;' );
            ADD( '--' );
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;

    ADD( '--' );
    ADD( '    PROCEDURE RESET;' );
    ADD( '--' );
    ADD( '    PROCEDURE DO_CASCADE;' );
    ADD( '--' );

    ADD( '    PROCEDURE ADD_ENTRY' );
    ADD( '    ( ' );
    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '        ' || L_COMMA || 'P_OLD_C' || STRIP(I) || ' IN ' ||
                  Q(P_TABLE_NAME) || '.' || Q(P_PKEY_NAMES(I)) || '%TYPE ' );
            L_COMMA := ',';
            ADD( '        ,P_NEW_C' || STRIP(I) || ' IN OUT ' ||
                  Q(P_TABLE_NAME) || '.' || Q(P_PKEY_NAMES(I)) || '%TYPE ' );
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( '     );' );
    ADD( '--' );
    ADD( 'END ' || PKG_NAME(P_TABLE_NAME) || ';' );

END WRITE_SPEC;

PROCEDURE WRITE_BODY
( P_TABLE_NAME         IN USER_CONSTRAINTS.TABLE_NAME%TYPE,
  P_PKEY_NAMES      IN CNAMEARRAY,
  P_CONSTRAINT_NAME    IN USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE )
IS
    L_COL_CNT         NUMBER DEFAULT 0;
    L_COMMA         CHAR(1) DEFAULT ' ';
    L_PKEY_STR      VARCHAR2(2000);
    L_PKEY_NAME_STR VARCHAR2(2000);
    L_OTHER_COL_STR VARCHAR2(2000);
BEGIN
    ADD( 'CREATE OR REPLACE PACKAGE BODY ' || PKG_NAME(P_TABLE_NAME) );
    ADD( 'AS' );
    ADD( '--' );

    ADD( '    PROCEDURE RESET ' );
    ADD( '    IS' );
    ADD( '    BEGIN' );
    ADD( '--' );
    ADD( '        IF ( INTRIGGER ) THEN RETURN; END IF;' );
    ADD( '--' );
    ADD( '        ROWCNT := 0;' );
    FOR I IN 1 .. 16 LOOP
        BEGIN
           IF (P_PKEY_NAMES(I) = P_PKEY_NAMES(I)) THEN
              L_COL_CNT := L_COL_CNT+1;
           END IF;
           ADD( '        OLD_C' || STRIP(I) || ' := EMPTY_C' || STRIP(I) || ';' );
           ADD( '        NEW_C' || STRIP(I) || ' := EMPTY_C' || STRIP(I) || ';' );
        EXCEPTION
           WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( '    END RESET;' );
    ADD( '--' );

    ADD( '    PROCEDURE ADD_ENTRY ' );
    ADD( '    ( ' );
    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '        ' || L_COMMA || 'P_OLD_C' || STRIP(I) || ' IN ' ||
                Q(P_TABLE_NAME) || '.' || Q(P_PKEY_NAMES(I)) || '%TYPE ' );
            L_COMMA := ',';
            ADD( '        ,P_NEW_C' || STRIP(I) || ' IN OUT ' ||
                  Q(P_TABLE_NAME) || '.' || Q(P_PKEY_NAMES(I)) || '%TYPE ' );
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( '     )' );
    ADD( '    IS' );
    ADD( '    BEGIN' );
    ADD( '--' );
    ADD( '        IF ( INTRIGGER ) THEN RETURN; END IF;' );
    ADD( '--' );
    ADD( '        IF ( ' );
    FOR I IN 1 .. L_COL_CNT LOOP
        IF ( I <> 1 ) THEN
            ADD( '            OR' );
        END IF;
        ADD( '             P_OLD_C' || STRIP(I) || ' <> ' ||
             'P_NEW_C' || STRIP(I) );
    END LOOP;
    ADD( '         ) THEN ' );
    ADD( '        ROWCNT := ROWCNT + 1;' );

    FOR I IN 1 .. L_COL_CNT LOOP
        ADD( '        OLD_C' || STRIP(I) ||
             '( ROWCNT ) := P_OLD_C' || STRIP(I) || ';' );
        ADD( '        NEW_C' || STRIP(I) ||
             '( ROWCNT ) := P_NEW_C' || STRIP(I) || ';' );
        ADD( '        P_NEW_C' || STRIP(I) ||
             ' := P_OLD_C' || STRIP(I) || ';' );
    END LOOP;

    ADD( '        END IF;' );
    ADD( '    END ADD_ENTRY;' );

    ADD( '--' );

    L_COMMA := ' ';
    FOR I IN 1 .. L_COL_CNT LOOP
        L_PKEY_STR      := L_PKEY_STR||L_COMMA||'$$_C' || STRIP(I) || '(I)';
        L_PKEY_NAME_STR := L_PKEY_NAME_STR || L_COMMA || Q(P_PKEY_NAMES(I));
        L_COMMA := ',';
    END LOOP;

    FOR X IN ( SELECT COLUMN_NAME
                 FROM USER_TAB_COLUMNS
                WHERE TABLE_NAME = P_TABLE_NAME
                  AND COLUMN_NAME NOT IN
                    ( SELECT COLUMN_NAME
                        FROM USER_CONS_COLUMNS
                       WHERE CONSTRAINT_NAME = P_CONSTRAINT_NAME )
                ORDER BY COLUMN_ID )
    LOOP
        L_OTHER_COL_STR := L_OTHER_COL_STR || ',' || Q(X.COLUMN_NAME);
    END LOOP;

    ADD( '    PROCEDURE DO_CASCADE' );
    ADD( '    IS' );
    ADD( '    BEGIN' );
    ADD( '--' );
    ADD( '        IF ( INTRIGGER ) THEN RETURN; END IF;' );
    ADD( '        INTRIGGER := TRUE;' );
    ADD( '--' );
    ADD( '        FOR I IN 1 .. ROWCNT LOOP' );
    ADD( '            INSERT INTO ' || P_TABLE_NAME || ' ( ' );
    ADD( '            ' || L_PKEY_NAME_STR );
    ADD( '            ' || L_OTHER_COL_STR || ') SELECT ');
    ADD( '            ' || REPLACE( L_PKEY_STR, '$$', 'NEW' ) );
    ADD( '            ' || L_OTHER_COL_STR );

    ADD( '            FROM ' || Q(P_TABLE_NAME) || ' A' );
    ADD( '            WHERE (' || L_PKEY_NAME_STR || ' ) = ' );
    ADD( '                  ( SELECT ' || REPLACE(L_PKEY_STR,'$$','OLD') );
    ADD( '                      FROM DUAL );' );
    ADD( '--' );
    IF ( PRESERVE_ROWID ) THEN
       ADD( '            UPDATE ' || Q(P_TABLE_NAME) || ' SET ' );
       ADD( '            ( ' || L_PKEY_NAME_STR || ' ) = ' );
       ADD( '            ( SELECT ' );
       FOR I IN 1 .. L_COL_CNT LOOP
          IF ( I <> 1 ) THEN ADD( '                  ,' ); END IF;
          ADD( '                 DECODE( ' || Q(P_PKEY_NAMES(I)) ||
          REPLACE(', OLD_C$$(I), NEW_C$$(I), OLD_C$$(I) )', '$$',STRIP(I)) );
       END LOOP;
       ADD( '              FROM DUAL )' );
       ADD( '            WHERE ( ' || L_PKEY_NAME_STR || ' ) =' );
       ADD( '                  ( SELECT ' || REPLACE(L_PKEY_STR,'$$','NEW') );
       ADD( '                      FROM DUAL )' );
       ADD( '               OR ( ' || L_PKEY_NAME_STR || ' ) =' );
       ADD( '                  ( SELECT ' || REPLACE(L_PKEY_STR,'$$','OLD') );
       ADD( '                      FROM DUAL );' );
    END IF;

    FOR X IN ( SELECT TABLE_NAME, CONSTRAINT_NAME
                 FROM USER_CONSTRAINTS
                WHERE R_CONSTRAINT_NAME = P_CONSTRAINT_NAME
                  AND CONSTRAINT_TYPE = 'R' ) LOOP

        L_COMMA := ' ';
        L_OTHER_COL_STR := '';
        FOR Y IN ( SELECT COLUMN_NAME
                     FROM USER_CONS_COLUMNS
                    WHERE CONSTRAINT_NAME = X.CONSTRAINT_NAME
                    ORDER BY POSITION ) LOOP
            L_OTHER_COL_STR := L_OTHER_COL_STR || L_COMMA || Q(Y.COLUMN_NAME);
            L_COMMA := ',';
        END LOOP;

        ADD( '--' );
        ADD( '            UPDATE ' || Q( X.TABLE_NAME ) || ' SET ');
        ADD( '            ( ' || L_OTHER_COL_STR || ' ) = ' );
        ADD( '            ( SELECT  ' || REPLACE( L_PKEY_STR, '$$', 'NEW' ) );
        ADD( '                FROM DUAL )' );
        ADD( '            WHERE ( ' || L_OTHER_COL_STR || ' ) = ' );
        ADD( '                  ( SELECT  ' ||
                                REPLACE( L_PKEY_STR, '$$', 'OLD' ) );
        ADD( '                    FROM DUAL );' );

    END LOOP;

    ADD( '--' );
    ADD( '            DELETE FROM ' || Q(P_TABLE_NAME)  );
    ADD( '             WHERE ( ' || L_PKEY_NAME_STR || ' ) = ' );
    ADD( '                   ( SELECT ' ||
    REPLACE( L_PKEY_STR, '$$', 'OLD' ) );
    ADD( '                       FROM DUAL);' );
    ADD( '        END LOOP;' );

    ADD( '--' );
    ADD( '        INTRIGGER := FALSE;' );
    ADD( '        RESET;' );
    ADD( '   EXCEPTION' );
    ADD( '       WHEN OTHERS THEN' );
    ADD( '          INTRIGGER := FALSE;' );
    ADD( '          RESET;' );
    ADD( '          RAISE;' );
    ADD( '    END DO_CASCADE;' );
    ADD( '--' );
    ADD( 'END ' || PKG_NAME( P_TABLE_NAME ) || ';' );

END WRITE_BODY;

PROCEDURE WRITE_BU_TRIGGER( P_TABLE_NAME IN USER_CONSTRAINTS.TABLE_NAME%TYPE,
                            P_PKEY_NAMES IN CNAMEARRAY )
IS
    L_COMMA CHAR(1) DEFAULT ' ';
BEGIN
    ADD( 'CREATE OR REPLACE TRIGGER ' || TRIGGER_NAME( P_TABLE_NAME, '1' ) );
    ADD( 'BEFORE UPDATE OF ' );
    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '   ' || L_COMMA || Q(P_PKEY_NAMES(I)) );
            L_COMMA := ',';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( 'ON ' || Q(P_TABLE_NAME) );
    ADD( 'BEGIN ' || PKG_NAME(P_TABLE_NAME) || '.RESET; END;' );
END WRITE_BU_TRIGGER;

PROCEDURE WRITE_BUFER_TRIGGER
( P_TABLE_NAME IN USER_CONSTRAINTS.TABLE_NAME%TYPE,
  P_PKEY_NAMES IN CNAMEARRAY )
IS
    L_COMMA   CHAR(1) DEFAULT ' ';
BEGIN
    ADD( 'CREATE OR REPLACE TRIGGER '||TRIGGER_NAME( P_TABLE_NAME, '2' ) );
    ADD( 'BEFORE UPDATE OF ' );

    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '   ' || L_COMMA || Q(P_PKEY_NAMES(I)) );
            L_COMMA := ',';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( 'ON ' || Q(P_TABLE_NAME) );
    ADD( 'FOR EACH ROW' );
    ADD( 'BEGIN ' );
    ADD( '   ' || PKG_NAME(P_TABLE_NAME) || '.ADD_ENTRY(' );

    L_COMMA := ' ';
    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '      ' || L_COMMA || ':OLD.' || Q(P_PKEY_NAMES(I)) );
            ADD( '      ,:NEW.' || Q(P_PKEY_NAMES(I)) );
            L_COMMA := ',';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( '      );' );
    ADD( 'END;' );
END WRITE_BUFER_TRIGGER;

PROCEDURE WRITE_AU_TRIGGER( P_TABLE_NAME IN USER_CONSTRAINTS.TABLE_NAME%TYPE,
                            P_PKEY_NAMES IN CNAMEARRAY )
IS
    L_COMMA  CHAR(1) DEFAULT ' ';
BEGIN
    ADD( 'CREATE OR REPLACE TRIGGER ' || TRIGGER_NAME( P_TABLE_NAME, '3' ) );
    ADD( 'AFTER UPDATE OF ' );
    FOR I IN 1 .. 16 LOOP
        BEGIN
            ADD( '   ' || L_COMMA || Q(P_PKEY_NAMES(I)) );
            L_COMMA := ',';
        EXCEPTION
            WHEN NO_DATA_FOUND THEN EXIT;
        END;
    END LOOP;
    ADD( 'ON ' || Q(P_TABLE_NAME) );
    ADD( 'BEGIN ' || PKG_NAME(P_TABLE_NAME) || '.DO_CASCADE; END;' );
END WRITE_AU_TRIGGER;

PROCEDURE ON_TABLE( P_TABLE_NAME      IN VARCHAR2,
                    P_PRESERVE_ROWID  IN BOOLEAN DEFAULT TRUE,
                    P_USE_DBMS_OUTPUT IN BOOLEAN DEFAULT FALSE )
IS
    L_TABLE_NAME         USER_CONSTRAINTS.TABLE_NAME%TYPE DEFAULT P_TABLE_NAME;
    L_CONSTRAINT_NAME   USER_CONSTRAINTS.CONSTRAINT_NAME%TYPE;
    L_PKEY_NAMES        CNAMEARRAY;
    L_COMMA                CHAR(1) DEFAULT ' ';
BEGIN
    USE_DBMS_OUTPUT := P_USE_DBMS_OUTPUT;
    PRESERVE_ROWID  := P_PRESERVE_ROWID;

    GET_PKEY_NAMES( L_TABLE_NAME, L_PKEY_NAMES, L_CONSTRAINT_NAME );

    SQL_STMT := NULL;
    WRITE_SPEC( L_TABLE_NAME, L_PKEY_NAMES );
    EXECUTE_IMMEDIATE;
    WRITE_BODY( L_TABLE_NAME, L_PKEY_NAMES, L_CONSTRAINT_NAME );
    EXECUTE_IMMEDIATE;
    WRITE_BU_TRIGGER( L_TABLE_NAME, L_PKEY_NAMES );
    EXECUTE_IMMEDIATE;
    WRITE_BUFER_TRIGGER( L_TABLE_NAME, L_PKEY_NAMES );
    EXECUTE_IMMEDIATE;
    WRITE_AU_TRIGGER( L_TABLE_NAME, L_PKEY_NAMES );
    EXECUTE_IMMEDIATE;

END ON_TABLE;

END UPDATE_CASCADE;
/

 

SQL상에서 UPDATE CASCADE할 Table명을 포함하여 실행하면 끝!

만일, TBL_MEMBER의 PK를 참조하는 다른 FK들에 대하여 UPDATE CASCADE를 한다면,

EXEC UPDATE_CASCADE.ON_TABLE('TBL_MEMBER');

라고 입력하면 된다.

이 Package 외에도 여러방법이 있으므로, 참조해 보도록 한다.

출처는 http://asktom.oracle.com/tkyte/update_cascade/

펼쳐두기..

UPDATE CASCADE PACKAGE

---

Generates needed package and triggers to support update cascade in Oracle without removing or infringing on DECLARITIVE RI.

This package supports:

• Tables with multi-part primary keys (primary key(a,c,b))
• Update cascade to many child tables from one parent
• Self-referencing integrity such as that found in the SCOTT.EMP table (mgr->empno)
• Is completely application transparent. The application does not know it is happening
• Versions 7.0 and above of the database.
• Tuned and optimized to fully avoid full table scans on all tables (complete with utility to show you un-indexed foreign keys in a schema, Cascading an update to un-indexed foreign keys can be bad).

This solution has the following restrictions:

1. All foreign keys to the parent table must point to the primary key constraint of the parent table. They cannot point to a unique constraint on the parent table, they must point to the primary key.
2. No other unique constraints/indexes may be in place on the parent table other then the primary key constraint.
3. Updates to primary keys that do not generate 'new' primary keys are not currently supported. For example, take the standard DEPT table. The update statement "update dept set deptno = deptno+10" will not work whereas the update "update dept set deptno = deptno+1" will. The first update will change 10->20, 20->30 and so on. Problem is that 10->20 is not generating a 'new' primary key. On the other hand, deptno=deptno+1 does not have this problem since 10->11, 20->21 and so on.
   NOTE: an update that affects a single row will never suffer from this problem.
4. the owner of the parent table must also be the owner of the child tables.
5. the owner of the parent table must run the following package in their schema. This package must be installed for each user that wants to generate update cascade support. It may be dropped after the cascade support has been generated.
6. the owner of the parent table must have been granted create procedure and create trigger. these priveleges may not be inherited from a role.

---

Installing, Using, and seeing how the demo works

---

This package consists of four SQL scripts

• uc.sql
• demobld.sql
• unindex.sql
• generate.sql

UC.SQL

UC.SQL should be run by any user wanting to implement update cascade. It will create

• A package spec for update_cascade
• A package body for update_cascade

Once this package is installed, you are able to implement update cascade on any table via the pl/sql call:

PROCEDURE update_cascade.on_table
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_TABLE_NAME                   VARCHAR2                IN
 P_PRESERVE_ROWID               BOOLEAN                 IN     DEFAULT
 P_USE_DBMS_OUTPUT              BOOLEAN                 IN     DEFAULT

Input Name	Default	Usage
p-table-name	NO DEFAULT	is the name of the parent table
p-preserve-rowid	TRUE	affects the generation of the code used to implement the generated packages. If set to TRUE (the default) the rowid of the updated parent row will not change due to the update. If set to FALSE, the rowid of the updated row WILL change BUT the code executes in about 66% of the time.
p-use-dbms-output	FALSE	defaults to FALSE which means the update_cascade package will execute (create) the packages/triggers. If set to true, the generated code will be printed using dbms_output.put_line (make sure to set serveroutput on size 100000 before using TRUE if you want to see the code).

For example....

SQL> REM will do the default cascade support on  
SQL> REM the dept table
SQL> REM ROWIDS will be preserved (unchanged).
SQL> REM Package will be created in the database
SQL> exec update_cascade.on_table( 'dept' )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table 
SQL> REM (the rowids will change).  This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one
SQL> exec update_cascade.on_table( 'dept', false )

SQL> REM will do the same as above but will not
SQL> REM preserve the rowids of the parent table 
SQL> REM (the rowids will change).  This version
SQL> REM runs in about 2/3 of the time of the first
SQL> REM one.  Also, the packages will be written
SQL> REM to the SCREEN, not into the database. 
SQL> exec update_cascade.on_table( 'dept', false, true )

The above would generate and compile all of the packages/triggers need to support cascading updates on the dept table to any other table. You would run this any time you

• changed the primary key definition of the dept table
• added a child table to the schema (eg: executed a create table and that table points to dept)
• removed a child table from schema

NOTE: The user executing update_cascade must have been granted

• CREATE TRIGGER
• CREATE PROCEDURE

directly to themselves. They cannot just have the privilege via a role.

The other mode of calling update_cascade.on_table is as follows:

SQL> set feedback off
SQL> spool tmp.sql
SQL> exec update_cascade.on_table( p_table_name => 'dept', p_use_dbms_output =>TRUE )
SQL> spool off

The above would generate and print (as opposed to compile) all of the packages/triggers/views needed to support cascading update on the dept table to any other table. You would use this mode to

• Avoid having to grant CREATE VIEW, CREATE TRIGGER, CREATE PROCEDURE directly to the person (they can get these priveleges via a role like DBA).
• To inspect the generated code to understand what it does.
• To generate a package that can be used to install update cascade support at another site without giving them the update_cascade package itself.

The other mode of calling update_cascade.on_table is as follows:

SQL> exec update_cascade.on_table( p_table_name => 'dept', p_preserve_rowid =>FALSE )

The above would generate faster versions of the udpate cascade packages. They run in 2/3 of the time of the default version but the rowid's of the updated parent records will change. This makes this version less desirable with FORMS. If you use forms heavily, use the default mode so that rowids are preserved. The triggers to cascade can get away with a lot less work in this mode. The triggers fire half the time they would in the default mode and an update that would normally fire and affect 2x the number of rows is not needed. The generated package code is streamlined as well (less actuall code is generated, procedural code not just updates).

DEMOBLD.SQL

This script creates one user and three tables in that users schema. The user is called UCDEMO. WARNING: This script does a "drop user ucdemo cascade". It is run as SYSTEM. Please review it before running it. Modify if you want. Once the script creates the user it will create six tables and populate them. The tables are:

• DEPT with a primary key
• EMP with a primar key, DECLARATIVE RI to DEPT, DECLARATIVE RI to EMP
• PROJECTS with a primary key, DECLARATIVE RI to EMP
• T1 with a three part primary key
• T2 with a three part primary key and a three part foreign key to T1
• T3 with a three part primary key and a three part foreign key to T2

To begin the demo, you might issue:

SQL> update dept set deptno=deptno+1;
update dept set deptno=deptno+1
       *
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005184) violated - child record
found

Then to see the update cascade in action, you would:

SQL> @uc
 
Table created.
 
Table altered.
 
Package created.
 
Package body created.

SQL> exec update_cascade.on_table('dept');
 
PL/SQL procedure successfully completed.
 
SQL> 

SQL> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
 
SQL> select empno, deptno from emp where deptno = 10;
 
     EMPNO     DEPTNO
---------- ----------
      7839         10
      7782         10
      7934         10
 
SQL> update dept set deptno = deptno+1;
 
4 rows updated.
 
SQL> select * from dept;
 
    DEPTNO DNAME          LOC
---------- -------------- -------------
        11 ACCOUNTING     NEW YORK
        21 RESEARCH       DALLAS
        31 SALES          CHICAGO
        41 OPERATIONS     BOSTON
 
SQL> select empno, deptno from emp where deptno in ( 10, 11 );
 
     EMPNO     DEPTNO
---------- ----------
      7839         11
      7782         11
      7934         11
 
SQL> 

As you can see, after the package is installed, the updates cascade successfully to the child tables. The effect of this is even more dramatic when you do it to the emp table. The MGR column of the EMP table points to the EMPNO column of the EMP table. In addition, the EMPNO column of the PROJECTS table points to the EMPNO column of the EMP table. The following scenario is a good demo of more complex integrity:

 
SQL> select empno, mgr from emp;
 
     EMPNO        MGR
---------- ----------
      7839
      7698       7839
      7782       7839
      7566       7839
      7499       7698
      7521       7698
      7654       7698
      7902       7566
      7369       7902
      7788       7566
      7844       7698
      7876       7788
      7900       7698
      7934       7782
 
14 rows selected.
 
SQL> update emp set empno = 8000 where empno = 7698;
update emp set empno = 8000 where empno = 7698
 *
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found
 
 
SQL> exec update_cascade.on_table('emp')
 
PL/SQL procedure successfully completed.
 
SQL> update emp set empno = 8000 where empno = 7698;
 
1 row updated.
 
SQL> select empno, mgr from emp;
 
     EMPNO        MGR
---------- ----------
      7839
      7782       7839
      7566       7839
      7499       8000
      7521       8000
      7654       8000
      7902       7566
      7369       7902
      7788       7566
      7844       8000
      7876       7788
      7900       8000
      7934       7782
      8000       7839
 
14 rows selected.
 

UNINDEX.SQL

The lack of an index on a foreign key will adversely impact the performance of a cascading update. For example, the emp table is created via:

CREATE TABLE EMP
(EMPNO NUMBER(4) primary key,
 ENAME VARCHAR2(10),
 JOB VARCHAR2(9),
 MGR NUMBER(4) references emp,
 HIREDATE DATE,
 SAL NUMBER(7,2),
 COMM NUMBER(7,2),
 DEPTNO NUMBER(2) references dept);

The MGR column and the DEPTNO column are not indexed by default. So, for example, if one were to issue:

SQL> update emp set empno = empno+1;

This would eventually issue:

update emp set mgr = SOME_NEW_VALUE
where mgr = SOME_OLD_VALUE;

Since the MGR field is not indexed, this update would do a full scan. It would do this full scan once for each row in EMP that was updated. Unindex.sql will generate output such as:

SQL> @unindex.sql
 
STAT TABLE_NAME                     COLUMNS              COLUMNS
---- ------------------------------ -------------------- --------------------
**** EMP                            MGR
ok   EMP                            DEPTNO               DEPTNO
ok   PROJECTS                       EMPNO                EMPNO, PROJ_NO
ok   T2                             A                    A, B
ok   T3                             A, B                 A, B, C
 
SQL> 

The four **** indicate that MGR is a foreign key in the EMP table that is not indexed. It should be (also see chapter 6 in the server application developers guide for other reasons why un-indexed foreign keys are bad).

GENERATE.SQL

This is a simple script that will generate all of the needed update_cascade.on_table( 'table_name' ) statements that need to be executed for all parent tables in a schema. For example, in the demo account it would create a spool file that contains:

SQL> @src/update_cascade/generate
 
prompt Update Cascade on table: DEPT
execute update_cascade.on_table( 'DEPT' )
 
prompt Update Cascade on table: EMP
execute update_cascade.on_table( 'EMP' )
 
prompt Update Cascade on table: T1
execute update_cascade.on_table( 'T1' )
 
prompt Update Cascade on table: T2
execute update_cascade.on_table( 'T2' )

---

HOW it works:

---

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 row
begin
   "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;