How to Implement a Cascade Update in a Master-deta

[mustafa76]

This document describes a method to perform a cascade update in a master-
detail table relationship. It is set up in a table-independent way, so the
routines in this article can be used to perform a cascade update from any
master table to all its detail tables. The routines use the referential
constraints created on the various tables.


SCOPE & APPLICATION
-------------------

This article is meant for any application developer or DBA who needs to perform
this kind of action. The package contains two examples (updating varchar2
and number columns), but it can be easily expanded to other datatypes or to
perform cascade deletes.

The routines provided below can be used as is; the only limitation is that when
they are invoked from a SQL*Forms-based application, you get no feedback on what
the routine is doing.


How to Perform a Cascade Update
-------------------------------

The package contains two public procedures. In both, the name of the
master table and the column to be changed has to be passed.
The old and new column value needs to be passed as well, in one routine
as varchar2, in the other as number.

Both routines perform similar actions:

- disable unique constraints on the given table and foreign key relations

- update the specified column with the provided value

- enable the unique constraints again

- get with a cursor all the tables containing foreign keys to the specified
column

- for each detail table, perform the given update and enable that particular
foreign key

-----------------------Code begins here-----------------------------------------

create or replace package ddl_actions is

procedure cascade_update (
p_table in varchar2,
p_column in varchar2,
org_value in number,
new_value in number);

procedure cascade_update (
p_table in varchar2,
p_column in varchar2,
org_value in varchar2,
new_value in varchar2);

end;

-----------------------------------------------------------------------
create or replace package body ddl_actions is

procedure check_new_value ( p_table in varchar2,
p_column in varchar2,
new_value in number) is
ds number;
str varchar2(200);
rows number;
x number;
begin

ds := dbms_sql.open_cursor;
str := ' begin select count(*) into :x from '||p_table||' where '||p_column||'='||to_char(new_value)||'; end;';
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
dbms_sql.bind_variable (ds, 'x', x);
rows := dbms_sql.execute (ds);
dbms_sql.variable_value (ds, 'x', x);

--dbms_output.put_line (to_char(x)||' row found with this value.');
dbms_sql.close_cursor (ds);
if x > 0 then
raise_application_error (-20000, 'There already exists a record in '||p_table||' with '||p_column||'='||to_char(new_value));
end if;
end;

------------------------------------------------------------------------
procedure check_new_value ( p_table in varchar2,
p_column in varchar2,
new_value in varchar2) is
ds number;
str varchar2(200);
rows number;
x number;
begin

ds := dbms_sql.open_cursor;
str := ' begin select count(*) into :x from '||p_table||' where '||p_column||'='''||new_value||'''; end;';
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
dbms_sql.bind_variable (ds, 'x', x);
rows := dbms_sql.execute (ds);
dbms_sql.variable_value (ds, 'x', x);

--dbms_output.put_line (to_char(x)||' row found with this value.');
dbms_sql.close_cursor (ds);
if x > 0 then
raise_application_error (-20000, 'There already exists a record in '||p_table||' with '||p_column||'='||new_value);
end if;
end;

------------------------------------------------------------------------
procedure disable_unique_constraint (p_table in varchar2, p_column in varchar2) is
ds number;
str varchar2(200);
cursor c_get_constraint_type (p_table in varchar2) is
SELECT UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
WHERE TABLE_NAME = p_table;
begin
for r_get_type in c_get_constraint_type (p_table) loop
if r_get_type.constraint_type in ('U','P') then
ds := dbms_sql.open_cursor;
if r_get_type.constraint_type = 'U' then
str := 'alter table '||p_table||' disable unique ('||p_column||') cascade';
else
str := 'alter table '||p_table||' disable primary key cascade';
end if;
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
dbms_sql.close_cursor (ds);
end if;
end loop;
end;

------------------------------------------------------------------------
procedure enable_unique_constraint (p_table in varchar2, p_column in varchar2) is
ds number;
str varchar2(200);
cursor c_get_constraint_type (p_table in varchar2) is
SELECT UC.CONSTRAINT_TYPE
FROM USER_CONSTRAINTS UC
WHERE TABLE_NAME = p_table;
begin
for r_get_type in c_get_constraint_type (p_table) loop
if r_get_type.constraint_type in ('U','P') then
ds := dbms_sql.open_cursor;
if r_get_type.constraint_type = 'U' then
str := 'alter table '||p_table||' enable unique ('||p_column||')';
else
str := 'alter table '||p_table||' enable primary key';
end if;
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
dbms_sql.close_cursor (ds);
end if;
end loop;
end;

------------------------------------------------------------------------
procedure enable_foreign_key (p_table in varchar2, p_constraint in varchar2) is
ds number;
str varchar2(200);
begin
ds := dbms_sql.open_cursor;
str := 'alter table '||p_table||' enable constraint '||p_constraint;
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
dbms_sql.close_cursor (ds);
end;

------------------------------------------------------------------------
procedure update_table ( p_table in varchar2,
p_column in varchar2,
org_value in number,
new_value in number) is
ds number;
str varchar2(200);
rows number;
begin
ds := dbms_sql.open_cursor;
str := 'update '||p_table||' set '||p_column||'='||to_char(new_value)||
' where '||p_column||'='||to_char(org_value);
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
rows := dbms_sql.execute (ds);
--dbms_output.put_line (to_char(rows)||' rows updated.');
dbms_sql.close_cursor (ds);

end;

------------------------------------------------------------------------
procedure update_table ( p_table in varchar2,
p_column in varchar2,
org_value in varchar2,
new_value in varchar2) is
ds number;
str varchar2(200);
rows number;
begin
ds := dbms_sql.open_cursor;
str := 'update '||p_table||' set '||p_column||'='''||new_value||
''' where '||p_column||'='''||org_value||'''';
--dbms_output.put_line (str);
dbms_sql.parse (ds, str, dbms_sql.native);
rows := dbms_sql.execute (ds);
--dbms_output.put_line (to_char(rows)||'rows updated.');
dbms_sql.close_cursor (ds);

end;

------------------------------------------------------------------------
procedure cascade_update (
p_table in varchar2,
p_column in varchar2,
org_value in number,
new_value in number) is

cursor c_get_children (p_table in varchar2, p_column in varchar2) is
SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = p_table AND COLUMN_NAME = p_column)
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;

l_table varchar2(50) := upper(p_table);
l_column varchar2(50) := upper(p_column);
begin
-- check whether new value is not already in use; if so, application-error is raised
check_new_value (p_table, p_column, new_value);

-- then disable unique constraints and all foreign-key relations
disable_unique_constraint (l_table, l_column);

-- now update parent
update_table (l_table, l_column, org_value, new_value);

-- and enable constraint
enable_unique_constraint (l_table, l_column);

-- now update all children
for r_get_children in c_get_children (l_table, l_column) loop
--dbms_output.put_line (r_get_children.table_name||'.'||r_get_children.column_name
-- ||' fk: ' ||r_get_children.constraint_name);
-- now update child-table
update_table (r_get_children.table_name, r_get_children.column_name, org_value,
new_value);

-- and enable the foreign-key-constraint again
enable_foreign_key (r_get_children.table_name, r_get_children.constraint_name);
end loop;
end;


------------------------------------------------------------------------
procedure cascade_update (
p_table in varchar2,
p_column in varchar2,
org_value in varchar2,
new_value in varchar2) is

cursor c_get_children (p_table in varchar2, p_column in varchar2) is
SELECT UC.CONSTRAINT_NAME, UC.TABLE_NAME, UCC.COLUMN_NAME
FROM USER_CONSTRAINTS UC, USER_CONS_COLUMNS UCC
WHERE R_CONSTRAINT_NAME IN (SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = p_table AND COLUMN_NAME = p_column)
AND UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME;


l_table varchar2(50) := upper(p_table);
l_column varchar2(50) := upper(p_column);
begin
-- check whether new value is not already in use; if so, application-error is raised
check_new_value (p_table, p_column, new_value);

-- then disable unique constraints and all foreign-key relations
disable_unique_constraint (l_table, l_column);

-- now update parent
update_table (l_table, l_column, org_value, new_value);

-- and enable constraint
enable_unique_constraint (l_table, l_column);

-- now update all children
for r_get_children in c_get_children (l_table, l_column) loop
--dbms_output.put_line (r_get_children.table_name||'.'||r_get_children.column_name
-- ||' fk: ' ||r_get_children.constraint_name);
-- now update child-table
update_table (r_get_children.table_name, r_get_children.column_name, org_value,
new_value);

-- and enable the foreign-key-constraint again
enable_foreign_key (r_get_children.table_name, r_get_children.constraint_name);
end loop;
end;

end;

---------------------------Code ends here---------------------------------------


Calling block to invoke this package from SQL*Plus:

SQL> execute ddl_actions.cascade_update ('DEPT', 'DEPTNO', 10, 15);

In this example, the DEPTNO (10) is changed (15) in DEPT and cascaded to EMP.

[Essam]

Thanks...