Post on 31-Dec-2015
05/05/2010
1
Disparadores de PL/SQLDisparadores de PL/SQL
Disparadores en
PL/SQL
2
DISPARADORESDISPARADORES� Bloques de PL/SQL nominados con las secciones:
– declarativa– ejecutable– manejo de excepciones
� Almacenados en la BD (diccionario de datos: user_triggers) y asociados a una tabla.
� Puede afectar a n filas.� Se ejecuta de manera implícita ante eventos
(operación de inserción, modificación o borrado sobre una BD)
� Se compila cada vez que lo activa --> más lentos
05/05/2010
2
Disparadores en
PL/SQL
3
DISPARADORES:AplicacionesDISPARADORES:Aplicaciones� Restricciones de Integridad complejas.
IMPORTANTE: no se deben usar para garantizar el cumplimiento de las RI a nivel de esquema !!! (el esquema ha de contener toda la semántica que permita sin utilizar disparadores)
� Auditoría: registro de los cambios realizados y quién los realizó
� Aviso automático a otros programas de llevar a cabo una determinada acción
� Actualización en cascada
Disparadores en
PL/SQL
4
DISPARADORES:UtilizaciónDISPARADORES:Utilización� No disparadores recursivos: agotan memoria.� Sólo se almacena el código fuente del
disparador. Se compila cada vez que se va a ejecutar (lectura del diccionario de datos). Por lo tanto si el disparador tiene más de 60 líneas de cuerpo conviene hacer un procedimiento.
� No utilizar para RI simples (a nivel de esquema).
� Identificador único para cada elemento.
05/05/2010
3
Disparadores en
PL/SQL
5
DISPARADORES:SintaxisDISPARADORES:Sintaxis� Creación: (se activan al crearlos)
CREATE [OR REPLACE] TRIGGER <nombre_disparador>{BEFORE | AFTER} evento ON referencia_tabla[ FOR EACH ROW [WHEN condición_evento]]
cuerpo_disparador;
� Eliminación:DROP TRIGGER nombre_disparador;
� Activación/Desactivación:ALTER TRIGGER nombre_disparador {DISABLE | ENABLE};
ALTER TABLE nombre_tabla{ENABLE | DISABLE} ALL TRIGGERS;
Disparadores en
PL/SQL
6
DISPARADORES:Componentes (1)DISPARADORES:Componentes (1)� Nombre disparador:
– Siguen las mismas normas de nomenclatura que otros identificadores en la BD
� Replace: – Se utiliza para sobreescribir un disparador existente
� Before/After:– Instante de ejecución del disparador con respecto al evento
� Evento:– Tipo de orden DML sobre una tabla que provoca la
activación del disparador {INSERT | DELETE | UPDATE [OF <lista de columnas>]}. La lista de columnas sólo tiene sentido en el evento UPDATE
05/05/2010
4
Disparadores en
PL/SQL
7
DISPARADORES: Componentes (2)DISPARADORES: Componentes (2)
� Nivel:– FOR EACH ROW: disparadores con nivel de fila. Se activan
una vez por cada fila afectada por el evento– FOR EACK STATEMENT: disparadores con nivel de orden.
Se activan sólo una vez (antes o después de la orden).
� When: Sólo tiene sentido a nivel de fila. La condición se evalúa (true o false). No se pueden utilizar consultas anidadas.
Disparadores en
PL/SQL
8
DISPARADORES: Componentes (3)DISPARADORES: Componentes (3)
� Cuerpo: bloque PL/SQL con las siguientes restricciones:– Un disparador no puede emitir ninguna orden de control de
transacciones (COMMIT, ROLLBACK o SAVEPOINT)– Ningún procedimiento o función llamada por el disparador
puede emitir órdenes de control de transacciones.– No puede contener ninguna declaración de variables LONG o
LONG RAW– Restricciones en tablas a las que se puede acceder (Tablas
Mutantes)– No puede modificar las columnas de PK
05/05/2010
5
Disparadores en
PL/SQL
9
Registros Registros :old :old yy :new:new� Un disparador con nivel de fila se ejecuta en cada
fila en la que se produce el suceso.� :old y :new son registros que nos permiten acceder a
los datos de la fila actual� Tipo de los registros: nombre_tabla%ROWTYPE;
Suceso :old :new
INSERT NULL Nuevos valores
UPDATE Valores almacenados Nuevos valores
DELETE Valores almacenados NULL
Disparadores en
PL/SQL
10
Ejemplo Ejemplo :old :old y y :new:newcreate sequence sec_estudiante start with 2;create table estudiante (codigo number(2) primary key
);CREATE OR REPLACE TRIGGER t_estudiante_B
BEFORE INSERT ON estudiante FOR EACH ROWBEGIN
SELECT sec_estudiante.nextval INTO :new.codigo FROM dual;END t_estudiante;� NOTAS:
– se ignoran los valores que se introducen como código de estudiante --> se inserta el siguiente de la secuencia
05/05/2010
6
Disparadores en
PL/SQL
11
INSERTING, DELETING Y UPDATINGINSERTING, DELETING Y UPDATING
� Predicados de los disparadores (booleanas), empleadas para determinar qué operación se está realizando en un disparador.
CREATE OR REPLACE TRIGGER CambiosBEFORE INSERT OR DELETE ON AlumnosFOR EACH ROW
DECLARECambio_tipo CHAR(1);
BEGIN/* Usa ‘I’ para INSERT y ‘D’ Para DELETE */IF INSERTING THEN
Cambio_tipo := ‘I’;
ELSE
Cambio_tipo := ‘D’;
END IF;
END Cambios;
Tablas MutantesTablas Mutantes
05/05/2010
7
Disparadores en
PL/SQL
13
Qué sonQué son� Tablas que están siendo modificadas por una
operación DML (INSERT, DELETE, UPDATE):
– En un disparador, la tabla sobre la que está definido
– Tablas que serán actualizadas como consecuencia de la integridad referencial (P.e.: DELETE_CASCADE)
Disparadores en
PL/SQL
14
En los disparadores (1)En los disparadores (1)� A nivel de FILA, dentro del cuerpo de un
disparador, no puede existir:– lecturas o modificaciones de tablas mutantes– cambio de clave primaria, claves ajenas o claves
alternativas de las tablas que restringen (el resto de las columnas sí se pueden cambiar)
– EXCEPCIÓN: no se dan las tablas mutantes en los disparadores con nivel de fila BEFORE INSERT
� A nivel de SENTENCIA no existen problemas de tablas mutantes– EXCEPCIÓN: si disparador se activa como consecuencia de
un BORRADO EN CASCADA (problemas de tablas mutantes)
05/05/2010
8
Disparadores en
PL/SQL
15
En los disparadores (2)En los disparadores (2)TIPO DE DISPARADOR ERROR DE TABLA MUTANTE
BEFORE INSERT ROW NO1
AFTER INSERT ROW SI
BEFORE INSERT STATEMENT NO
AFTER INSERT STATEMENT NO
BEFORE DELETE ROW SI
AFTER DELETE ROW SI
BEFORE DELETE STATEMENT NO2
AFTER DELETE STATEMENT NO2
BEFORE UPDATE ROW SI
AFTER UPDATE ROW SI
BEFORE UPDATE
STATEMENT
NO
AFTER UPDATE STATEMENT NO
1 Siempre que la inserción que provoque la activación del disparador sea una
inserción simple (se inserte una única fila).2 Siempre que el disparador no se active como consecuencia de un borrado en
cascada. En ese caso, aparecerá también un error de tabla mutante.
Disparadores en
PL/SQL
16
Ejemplo (1)Ejemplo (1)� “Una zona tiene uno o varios departamentos y
un departamento trabaja en una o ninguna zona”.
CREATE SEQUENCE Secuencia_DepartamentoSTART WITH 100000INCREMENT BY 1;
05/05/2010
9
Disparadores en
PL/SQL
17
Ejemplo (2)Ejemplo (2)
CREATE TABLE Zona (Cod_Zona NUMBER(6) CONSTRAINT pk_zona PRIMARY KEY,Nom_Zona VARCHAR2(40) NOT NULL
);
CREATE TABLE Departamento (Cod_Dep NUMBER(6) CONSTRAINT pk_departamento PRIMARY KEY,Presupuesto NUMBER(8) NOT NULL,Cod_Zona NUMBER(2) NOT NULL
CONSTRAINT fk_departamento_zona REFERENCESZona(Cod_Zona) ON DELETE CASCADE
);
Disparadores en
PL/SQL
18
Ejemplo 1 (1)Ejemplo 1 (1)� EJEMPLO 1:
CREATE OR REPLACE TRIGGER Disparador1AFTER INSERT ON Zona FOR EACH ROW
BEGININSERT INTO Departamento VALUES(Secuencia_Departamento.NEXTVAL, 10000000, :new.Cod_Zona);
END Disparador1;/
Operación:INSERT INTO Zona VALUES (1, ‘CENTRO’);
05/05/2010
10
Disparadores en
PL/SQL
19
Ejemplo 1 (2)Ejemplo 1 (2)� EJEMPLO 1. Comentarios:
– La tabla departamento referencia a la tabla zona (FK).
– Cada vez que se inserta un nuevo dato en la tabla departamento, Oracle controla la integridad referencial (el código Departamento.Cod_Zona ha de existir en la tabla Zona --> Realiza una lectura de la tabla Zona, que está mutando !!)
– NOTA: Si en lugar de realizar una inserción en la tabla departamento, hubiéramos realizado una selección, no habría dado error de tabla mutante.
Disparadores en
PL/SQL
20
Ejemplo 2 (1)Ejemplo 2 (1)� EJEMPLO 2:
CREATE OR REPLACE TRIGGER Disparador2AFTER INSERT ON Departamento FOR EACH ROW
DECLAREVar Departamento%ROWTYPE;
BEGINUPDATE Zona SET Nom_Zona='U' WHERE Cod_Zona=:new.Cod_Zona;
END Disparador2;/
Operación:INSERT INTO Departamento VALUES (Secuencia_Departamento.NEXTVAL,
20000000, 1);
05/05/2010
11
Disparadores en
PL/SQL
21
Ejemplo 2 (2)Ejemplo 2 (2)� EJEMPLO 2. Comentario:
– No existe error de tabla mutante: la tabla departamento referencia a la tabla zona, por lo que no se pueden modificar sus claves, pero sí se pueden modificar las demás columnas.
– En el caso de que el disparador fuese BEFORE insert row, no existiría error de tabla mutante ni siquiera si se modifican sus claves.
Disparadores en
PL/SQL
22
Ejemplo 3 (1)Ejemplo 3 (1)� EJEMPLO 3:
– Creación de una tabla independiente a las anteriores:
CREATE SEQUENCE Secuencia_MensajeSTART WITH 100000
INCREMENT BY 1;
CREATE TABLE Mensaje_Departamento (Cod_Mensaje NUMBER(6) CONSTRAINT pk_error PRIMARY KEY,Cod_Dep NUMBER(6) NOT NULL,Tipo VARCHAR2(255) NOT NULL,Fecha DATE NOT NULL
);
05/05/2010
12
Disparadores en
PL/SQL
23
Ejemplo 3 (2)Ejemplo 3 (2)� EJEMPLO 3. Disparador:
CREATE OR REPLACE TRIGGER Disparador3AFTER INSERT ON DepartamentoFOR EACH ROW
BEGININSERT INTO Mensaje_Departamento VALUES
(Secuencia_Mensaje.NEXTVAL, :new.Departamento, 'Presupuestoelevado', SYSDATE);
END Disparador3;/
Operación:INSERT INTO Departamento VALUES (Secuencia_Departamento.NEXTVAL,
70000, 1);
Disparadores en
PL/SQL
24
Ejemplo 3 (3)Ejemplo 3 (3)� EJEMPLO 3. Comentarios:
– No existe error de tabla mutante: la tabla mensaje_departamento es independiente de la tabla departamento.
05/05/2010
13
Disparadores en
PL/SQL
25
Ejemplo 4 (1)Ejemplo 4 (1)� EJEMPLO 4:
CREATE OR REPLACE TRIGGER Disparador4BEFORE DELETE ON Departamento FOR EACH ROW
DECLAREVar Zona%ROWTYPE;
BEGINSELECT * INTO Var FROM Zona WHERE Cod_Zona=:old.Cod_Zona;
END Disparador4;/
Operación1: DELETE FROM Departamento WHERE Cod_Zona=1;
Operación2: DELETE FROM Zona WHERE Cod_Zona=1;
Disparadores en
PL/SQL
26
Ejemplo 4 (2)Ejemplo 4 (2)� EJEMPLO 4. Comentarios:
– Operación 1: No da error de tabla mutante: departamento referencia a la tabla zona, que sí se puede consultar, ya que no está mutando.
– Operación 2: Da error de tabla mutante, ya que, al borrar en la tabla zona (tabla mutante), se borran todas las las tuplas de la tabla departamentoque referencian a la zona borrada. Esto activa el disparador4 de departamento, que consulta la tabla zona, que en este caso sí esta mutando.
05/05/2010
14
Disparadores en
PL/SQL
27
Ejemplo 5 (1)Ejemplo 5 (1)� EJEMPLO 5:
CREATE OR REPLACE TRIGGER Disparador5AFTER DELETE ON Departamento
DECLAREVar Zona%ROWTYPE;
BEGINSELECT * INTO Var FROM Zona WHERE Cod_Zona=1;
END Disparador5;/
Operación 1: DELETE FROM Zona WHERE Cod_Zona = 1;
Operación 2: DELETE FROM Departamento WHERE Cod_Zona = 1;
Disparadores en
PL/SQL
28
Ejemplo 5 (2)Ejemplo 5 (2)� EJEMPLO 5. Comentarios:
– Operación 1: Error de tabla mutante.� La excepción en los disparadores a nivel de sentencia se
encuentra en los borrados en cascada, al leer o modificar una tabla mutante.
� Al borrar de la tabla zona, se desencadena un borrado en cascada en la tabla departamento (ambas tablas mutantes), y al mismo tiempo intenta leer de la tabla zona.
– Operación 2: NO hay error.� Al borrar de la tabla departamento no se desencadena ningún
borrado en cascada (sólo borra de la tabla departamento)
05/05/2010
15
Disparadores en
PL/SQL
29
Modificación en Cascada (1)Modificación en Cascada (1)� EJEMPLO 6:
CREATE OR REPLACE TRIGGER Modificacion_CascadaBEFORE UPDATE OF Cod_Zona ON Zona FOR EACH ROW
BEGINUPDATE Departamento SET Cod_Zona = :new.Cod_Zona
WHERE Cod_Zona = :old.Cod_Zona;END Modificacion_Cascada;/
Operación:
UPDATE Zona SET Cod_Zona = 2 WHERE Cod_Zona = 1;
Disparadores en
PL/SQL
30
Modificación en Cascada (2)Modificación en Cascada (2)� EJEMPLO 6. Comentarios:
– Error de tabla mutante
– La tabla departamento, que referencia a la tabla zona (FK), al modificarse, se han de comprobar las restricciones de integridad (consultar zona, que está mutando).
05/05/2010
16
Disparadores en
PL/SQL
31
* La modificación en cascada las vamos a resolver con dos disparadores en fila, “engañando“ a ORACLE.1.- Modificación BEFORE
CREATE TRIGER Modificación_Cascada_Before BEFORE UPDATE OF DNI ON zona FOR EACH ROW . . .
- Registra los datos en el paquete, capturando las imágenes previa y posterior de la clave de acceso.
- Por último, deshace la operación (vuelve la clave a su valor original)
2.- Modificación AFTERCREATE TRIGER Modificación_Cascada_After
AFTER UPDATE OF DNI ON zona . . .- Duplica la fila a modificar, pero con la nueva clave - Actualiza los registros referenciantes (hijos)- Borra el registro replicado (el viejo)
Modificación en Cascada. Modificación en Cascada. Solución (1)Solución (1)
Disparadores en
PL/SQL
32
CREATE PACKAGE DatosZona AS
TYPE t_CodZona IS TABLE OF Zona.Cod_Zona%TYPE INDEX BY BINARY_INTEGER;
t_CodZonaOld t_CodZona;
t_CodZonaNew t_CodZona;
num_entradas BINARY_INTEGER:=0;
END DatosZona;
/
Modificación en Cascada. Modificación en Cascada. Solución (2)Solución (2)
05/05/2010
17
Disparadores en
PL/SQL
33
CREATE OR REPLACE TRIGGER Modificacion_Cascada_Before
BEFORE UPDATE OF Cod_Zona ON Zona FOR EACH ROW
BEGIN
DatosZona.num_entradas := DatosZona.num_entradas+1;
DatosZona.t_CodZonaOld(DatosZona.num_entradas) := :old.Cod_Zona;
DatosZona.t_CodZonaNew(DatosZona.num_entradas) := :new.Cod_Zona;
:new.Cod_Zona := :old.Cod_Zona;
END Modificacion_Cascada_Before;
/
Modificación en Cascada. Modificación en Cascada. Solución (3)Solución (3)
Disparadores en
PL/SQL
34
CREATE OR REPLACE TRIGGER Modificacion_Cascada_AfterAFTER UPDATE OF Cod_Zona ON ZonaDECLARE CodZonaOld Zona.Cod_Zona%TYPE;CodZonaNew Zona.Cod_Zona%TYPE;BEGINFOR i IN 1..DatosZona.num_entradas LOOPCodZonaOld := DatosZona.t_CodZonaOld(i);CodZonaNew := DatosZona.t_CodZonaNew(i);INSERT INTO Zona
SELECT CodZonaNew, Nom_Zona FROM Zona WHERE Cod_Zona = CodZonaOld;UPDATE Departamento
SET Cod_Zona = CodZonaNew WHERE Cod_Zona = CodZonaOld;DELETE FROM Zona WHERE Cod_Zona = CodZonaOld;END LOOP;DatosZona.num_entradas := 0;END Modificacion_Cascada_After;/
Modificación en Cascada. Modificación en Cascada. Solución (4)Solución (4)