COMO HACER "UPDATE CASCADE"
A fecha de hoy, 25-11-2005, no existe ninguna funcionalidad de Oracle similar al "ON DELETE CASCADE", para el caso de hacer un UPDATE; es decir, si hacemos UPDATE de una tabla de forma que modifiquemos la PK, Oracle no hace UPDATE de las FKs de las tablas que hacen referencia a la mencionada PK. Esto ocurre con todas las versiones actuales de Oracle (incluidas 9i y 10g); es más, al intentar hacer el UPDATE de la PK (habiendo FKs que la referencian), dará un error ORA-02292 ("integrity constraint violated - child record found").
Una forma de solucionar ésto es haciendo uso de triggers; de forma que, por ejemplo, podamos crear una fila con la nueva PK, actualizar las FKs (pues ya existe el nuevo valor), y finalmente borrar la fila con la PK antigua.
Esto puede ser tedioso de programar y mantener. Por ello, y con el fin de automatizar el proceso de generar el código (paquetes y triggers) necesario para hacer el UPDATE CASCADE de una tabla, he instalado en las bases de datos de desarrollo y explotación el paquete MNCS.UPDATE_CASCADE.
SOLUCION PROPUESTA
Existe una implementación, por parte de un "gurú" de Oracle (asktom.oracle.com), de un paquete para hacer un UPDATE CASCADE de una tabla q tiene una PK y existen FKs apuntando a ella. Está todo muy bien explicado, incluso con ejemplos, en:
He instalado en ISIS y VENUS el paquete, UPDATE_CASCADE, q propone; modifcándolo para inlcuir "AUTHID CURRENT_USER", de modo que se pueda instalar el paquete en un sólo esquema (el de MNCS), y q lo pueda ejecutar cualquier usuario autorizado (q tenga el rol ADMAPL).
EJEMPLO DE USO
Si quieres hacer un UPDATE sobre una tabla, modifcando el valor de alguna columna de la PK, y esta tabla tiene FKs referenciándola; si no quieres obtener una ora-2292, puedes usar el paquete MNCS.UPDATE_CASCADE. Está instalado en ISIS y VENUS, bajo el esquema MNCS, y existe un sinónimo público. Sólo podrás usarlo desde un usuario q tenga asignado el rol ADMAPL.
Veamos el ejemplo:
SQL> update dept set deptno = deptno+1;
update dept set deptno = deptno+1
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C00116448) violated - child record
found
SQL> exec update_cascade.on_table('dept');
PL/SQL procedure successfully completed.
SQL> update dept set deptno = deptno+1;
4 rows updated.
Se observa como obtenemos el error ora-02292 al intentar hacer un UPDATE de DEPT.DEPTNO (pues tiene la FK EMP.DEPTNO). Ejecutando UPDATE_CASCADE.ON_TABLE('dept'), se crea de forma automática los paquetes y triggers necesarios para evitar el error y que se consume el UPDATE de la tabla EMP, y automáticamente también en la tabla DEPT.
INSTRUCCIONES DE USO
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
| PARAMETRO |
VALOR POR DEFECTO |
USO |
| p-table-name |
NO DEFAULT |
nombre de la tabla padre (la q tiene la PK)
|
| p-preserve-rowid |
TRUE |
TRUE: los ROWID de las filas de la tabla padre no cambiarán como conscuencia de un UPDATE. Recomendado para tablas accedidas frecuentemente desde Forms, siendo la actualización simultánea a tal acceso).
FALSE: el ROWID cada fila de la tabla padre actualizada cambiará, pero consigue una mejora en tiempo de ejecución del update del 66%.
|
| p-use-dbms-output |
FALSE |
FALSE: se crean los paquetes y triggers necesarios para que al hacer UPDATE sobre la PK de la tabla padre, tb se haga update de las FKs de las tablas q las tengan.
TRUE: no se crean los paquetes, sólo se muestran por pantalla. Hay q activar la salida por pantalla con 100000 (ejecutando "exec dbms_output.enable(100000);".
|
¡¡¡ OJO !!!, una vez que ejecutemos UPDATE_CASCADE.ON_TABLE('TABLA'), se crean los paquetes y triggers para automatizar el UPDATE de las tablas hijas; de modo, q sólo se hace una vez y si queremos que deje de hacerlo habremos de borrar los paquetes y triggers creados.
PREGUNTAS FRECUENTES
- ¿Tengo que ejecutar update_cascade.on_table cada vez que quiera hacer un UPDATE de una tabla que afecte a su PK?
No. Sólo será necesario si al hacer el UPDATE obtienes una error ora-02292; en cuyo caso, ejecutando el procedimiento update_cascade.on_table('nombre_tabla'), estás creando automáticamente los paquetes y triggers necesarios para poder hacer el UPDATE anterior sin que casque. Los paquetes y triggers creados como consecuencia de la ejecución del update_cascade.on_table seguirán ahí para siempre (a no ser q los borres), y por tanto, en posteriores UPDATES no tendrás q volver a hacer el updadate_cascade.on_table.
- ¿Qué "paquetes y triggers" se crean al ejecutar el update_cascade.on_table?
Si quieres ver el código que se va a crear, antes de ejecutarlo de verdad puedes hacerlo con update_cascade.on_table('nombre_tabla', true, true): SQL> set serveroutput on
SQL> exec dbms_output.enable(100000)
SQL> exec update_cascade.on_table('dept',true,true)
create or replace package "uDEPTp"
as
--
rowCnt number default 0;
inTrigger boolean default FALSE;
--
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;
--
--
procedure reset;
--
procedure do_cascade;
--
procedure add_entry
(
p_old_C1 in "DEPT"."DEPTNO"%type
,p_new_C1 in out "DEPT"."DEPTNO"%type
);
--
end "uDEPTp";
/
create or replace package body "uDEPTp"
as
--
procedure reset
is
begin
--
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;
--
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 i in 1 .. rowCnt loop
insert into DEPT (
"DEPTNO"
,"DNAME","LOC") select
new_C1(i)
,"DNAME","LOC"
from "DEPT" a
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--
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 );
--
update "EMP" set
( "DEPTNO" ) =
( select new_C1(i)
from dual )
where ( "DEPTNO" ) =
( select old_C1(i)
from dual );
--
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";
/
create or replace trigger "uDEPT1"
before update of
"DEPTNO"
on "DEPT"
begin "uDEPTp".reset; end;
/
create or replace trigger "uDEPT2"
before update of
"DEPTNO"
on "DEPT"
for each row
begin
"uDEPTp".add_entry(
:old."DEPTNO"
,:new."DEPTNO"
);
end;
/
create or replace trigger "uDEPT3"
after update of
"DEPTNO"
on "DEPT"
begin "uDEPTp".do_cascade; end;
/
PL/SQL procedure successfully completed.
- Si quiero que no cambien los ROWID de las filas afectadas por el UPDATE, ¿qué tengo que hacer?
Por defecto, el código generado funciona de forma que para cada fila cuya PK cambia, se crea una fila y se borra la antigua, provocando un cambio en los ROWID. Si quiero que no haya cambios de ROWID, haré update_cacade.on_table('nombre_tabla',false).
Fecha de publicación: 25/11/2005
Autor: Juan Luis Serradilla Amarilla. juanlu@um.es |
