lopezatienza.com

DISPARADORES

 

·         Bloques de PL/SQL nominados con las secciones:

– declarativa

– ejecutable

– manejo de excepciones

·         Almacenados en la BD (diccionario de datos: user_triggers).

·         Tipos de Disparadores:

– LMD sobre tablas

– Disparadores INSTEAD OF sobre vistas

– Disparadores del sistema sobre la BD o el Esquema.

·         Se ejecuta de manera implícita ante eventos:

– LMD sobre tablas (Insert, Delete, Update)

– LDD (Create, Alter, Drop) (Actualizado con Oracle 9i)

– Operaciones de la BD (ServerError, Logon, Logoff, Startup, Shutdown) (Actualizado con Oracle 9i)

DISPARADORES: 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: Utilización

 

·         Identificador único para cada elemento.

·         Tamaño del disparador: no superar los 32K.

·         Operaciones en el cuerpo del disparador: LMD.

·         No disparadores recursivos: agotan memoria.

·         Compilación cada vez que se ejecutan: más lentos.

 

DISPARADORES: 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: Componentes

 

·         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

·         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. (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 clave primaria

 

Registros :old y :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

 

Ejemplo :old y :new

 

create 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 ROW

BEGIN

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

 

INSERTING, DELETING Y UPDATING

 

·         Predicados de los disparadores (booleanos), empleadas para determinar qué operación se está realizando en un disparador.

 

CREATE OR REPLACE TRIGGER Cambios

BEFORE INSERT OR DELETE ON Alumnos

FOR EACH ROW

DECLARE

Cambio_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 MUTANTES

 

·         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)

 

En los disparadores

·         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)

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                         NO

STATEMENT

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.

 

Ejemplo

·         “Una zona tiene uno o varios departamentos y un departamento trabaja en una o ninguna zona”.

 

CREATE SEQUENCE Secuencia_Departamento

START WITH 100000

INCREMENT BY 1;

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 REFERENCES

Zona(Cod_Zona) ON DELETE CASCADE

);

 

Mas ejemplos1 (1)

 

·         EJEMPLO 1:

·          

CREATE OR REPLACE TRIGGER Disparador1

AFTER INSERT ON Zona FOR EACH ROW

BEGIN

INSERT INTO Departamento VALUES

(Secuencia_Departamento.NEXTVAL, 10000000, :new.Cod_Zona);

END Disparador1;

/

Operación:

INSERT INTO Zona VALUES (1, ‘CENTRO’);

1      (2)

EJEMPLO 1. Comentarios:

 

– La versión Oracle 8i arregla este problema de tabla mutante (NO DA ERROR).

§  Ahora Oracle produce un error al realizar una consulta para comprobar si existe una Zona con ese Cod_Zona al insertar en Departamento.

– Versiones anteriores: Error de tabla mutante

§  La tabla departamento referencia a la tabla zona (FK).

§  Cada vez que se inserta un nuevo dato en la tabla departamento, Oracle controlaba 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, se hubiera realizado una selección, no habría dado error de tabla mutante.

2 (1)

• EJEMPLO 2:

 

CREATE OR REPLACE TRIGGER Disparador2

AFTER INSERT ON Departamento FOR EACH ROW

DECLARE

Var Departamento%ROWTYPE;

BEGIN

UPDATE Zona SET Nom_Zona='U' WHERE Cod_Zona=:new.Cod_Zona;

END Disparador2;

/

 

Operación:

INSERT INTO Departamento VALUES (Secuencia_Departamento.NEXTVAL, 20000000, 1);

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.

 

• EJEMPLO 3:

 

– Creación de una tabla independiente a las anteriores:

 

CREATE SEQUENCE Secuencia_Mensaje

START 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

);

3 (2)

EJEMPLO 3. Disparador:

 

CREATE OR REPLACE TRIGGER Disparador3

AFTER INSERT ON Departamento

FOR EACH ROW

BEGIN

INSERT INTO Mensaje_Departamento VALUES

(Secuencia_Mensaje.NEXTVAL, :new.Departamento, 'Presupuesto

elevado', SYSDATE);

END Disparador3;

/

 

Operación:

INSERT INTO Departamento VALUES (Secuencia_Departamento.NEXTVAL,

70000, 1);

Ejemplo 3 (3)

EJEMPLO 3. Comentarios:

– No existe error de tabla mutante: la tabla

mensaje_departamento es independiente de la tabla

departamento.

4 (1)

• EJEMPLO 4:
•  

CREATE OR REPLACE TRIGGER Disparador4

BEFORE DELETE ON Departamento FOR EACH ROW

DECLARE

Var Zona%ROWTYPE;

BEGIN

SELECT * 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;

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:

§  La versión Oracle 8i arregla el problema de tabla mutante (mismo caso que el ejercicio 1).

§  Versiones anteriores de Oracle: Da error de tabla mutante, ya que, al borrar en la tabla zona (tabla mutante), se borran todas las las tuplas de la tabla departamento que referencian a la zona borrada. Esto activa el disparador4 de departamento, que consulta la tabla zona, que en este caso sí esta mutando.

5 (1)

• EJEMPLO 5:

 

CREATE OR REPLACE TRIGGER Disparador5

AFTER DELETE ON Departamento

DECLARE

Var Zona%ROWTYPE;

BEGIN

SELECT * 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;

5 (2)

• EJEMPLO 5. Comentarios:

 

– Operación 1:

§  Versión Oracle 8i: No existe error de tabla mutante.

– En el caso que en el cuerpo del disparador se consulte exactamente las tuplas que se están borrando, dará error el disparador indicando que “no se encuentran datos”.

§  Versiones anteriores: Error de 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)

Modificación en Cascada (1)

• EJEMPLO 6:

 

CREATE OR REPLACE TRIGGER Modificacion_Cascada

BEFORE UPDATE OF Cod_Zona ON Zona FOR EACH ROW

BEGIN

UPDATE 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;

Modificación en Cascada (2)

EJEMPLO 6. Comentarios:

 

– Oracle 8i: Error de restricción única.

§  Se intenta modificar la tupla en Zona, pero como hay tuplas en Departamento que hacen referencia a las tuplas de Zona que se están borrando, surge el error de restricción única (FK).

– Oracle versiones anteriores: 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).

Autor: Antonio Lopez Atienza