disparadores en oracle - kybele · estructurado en bloques bloques: unidad mínima en pl/sql...
TRANSCRIPT
Disparadores en ORACLE
Disparadores
Tema 11
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Introducción
PL/SQL: lenguaje de programación estructurado en bloques
Bloques: unidad mínima en PL/SQL Soportan DML y DDL
Anónimos / Con nombre → TRIGGERS
Disparadores (triggers) en Oracle: bloques de código que son implícitamente invocados cuando algo sucede
Triggers vs. Procedimientos Ejecución implícita: Disparar
No admiten argumentos
Aplicaciones Restricciones (Constraints)
Auditorías
Informar de eventos
DECLARE ▬ optional BEGIN ▬ required EXCEPTION ▬ optional END;
required /
CREATE PROCEDURE Get_emp_rec
(Emp_number IN
Emp_tab.Empno%TYPE) AS
BEGIN
- - - - -
END;
/
CREATE TRIGGER
NombreTrigger
BEFORE INSERT ON StarsIn
DECLARE
…..
END;
/ TRIGGER
PROCEDIMIENTO
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Introducción
3 Tipos DML/DDL (Fila/Sentencia, BEFORE/AFTER)
INSTEAD OF
SYSTEM
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Estructura General de un Disparador
CREATE [OR REPLACE] TRIGGER nombre {BEFORE | AFTER | INSTEAD OF} Temporalidad del Evento {INSERT | DELETE | UPDATE [OF <atributo>]} ON <tabla>
[FOR EACH ROW | STATEMENT] Granularidad [WHEN condición] [DECLARE]
…
BEGIN
cuerpo del trigger
[EXCEPTION]
…
END;
/
Evento
Condición
Acción
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Estructura General de un Disparador Ejemplo
CREATE OR REPLACE TRIGGER generaNuevoPedido AFTER UPDATE OF unid_disponibles ON Inventario
FOR EACH ROW WHEN (new.unid_disponibles < new.unid_solicitadas)
DECLARE x NUMBER; BEGIN
-- Número de pedidos pendientes de ese producto SELECT COUNT(*) INTO x FROM PedidosPendientes WHERE num_producto=:num_producto;
-- Si no hay ninguna orden, hacer el pedido IF x = 0 THEN
INSERT INTO PedidosPendientes VALUES (:new.num_producto, :new.cantidad_pedido, SYSDATE); END IF; END;
Evento
Condición
Acción
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
BEFORE Ejecutan la acción asociada ANTES de que la sentencia sea ejecutada
Decidir si la acción debe realizarse
Utilizar valores alternativos para la sentencia CREATE TRIGGER NombreTrigger
BEFORE Insert ON NombreTabla ….
AFTER Ejecutan la acción asociada DESPUÉS de que se haya ejecutado la sentencia
CREATE TRIGGER NombreTrigger
AFTER Insert ON NombreTabla ….
Temporalidad del Evento: AFTER / BEFORE
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
A NIVEL DE FILA: ROW TRIGGERS Ejecutan la acción asociada tantas veces como filas se vean afectadas por la sentencia que lo dispara
Si ninguna fila se ve afectada, no se dispara
CREATE TRIGGER NombreTrigger
BEFORE Insert ON NombreTabla
FOR EACH ROW ….
Granularidad del Evento FOR EACH ROW / STATEMENT
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
A NIVEL DE SENTENCIA: STATEMENT TRIGGERS Ejecutan una única vez la acción asociada, independientemente del número de filas que se vean afectadas por la sentencia
CREATE TRIGGER NombreTrigger
BEFORE Insert ON NombreTabla
[STATEMENT]
Granularidad del Evento FOR EACH ROW / STATEMENT
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
ROW TRIGGER Ejemplo
Persona
Cod Nombre Edad C1 C2 C3 C4 C5
María Pepe Pepe Luisa Pepe
25 40 45 48 22
Persona2
Cod Nombre Edad C1 C2 C3 C4 C5
María Pepe Pepe Luisa Pepe
25 40 45 48 22
Cuando se borre en la tabla Persona alguna persona que se llame “pepe” o cuya edad sea mayor de 35 años, eliminar también dicha persona de la tabla Persona2
DELETE FROM Persona
WHERE cod in (‘C1’,‘C3’,‘C4’) Borra C3 y C4 de Persona2
A NIVEL DE FILA
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
STATEMENT TRIGGER Ejemplo
Borra 3 tuplas y se emite un único mensaje
Socio
Cod Nombre Fecha_ant S1 S2 S3 S4 S5
María Pepe Pepe Luisa Pepe
......
......
......
......
......
Cuando se borre en la tabla socio emitir un mensaje indicando que no se pueden borrar socios
DELETE FROM socio
WHERE nombre = ‘Pepe’
A NIVEL DE SENTENCIA
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Condición
Expresa una condición que debe cumplirse en el momento de producirse el evento, para que la acción sea ejecutada.
Debe ser una expresión booleana y no puede contener subconsultas
Se puede utilizar cualquier combinación de operadores lógicos (AND, OR, NOT) y relacionales (< <= > >= = <>).
No se puede especificar una condición para los disparadores a nivel de sentencia (STATEMENT) ni los disparadores INSTEAD OF
WHEN persona.nombre = 'pepe' OR persona.edad > 35
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Orden de Ejecución
Una sentencia SQL puede disparar varios TRIGGERS.
La activación de un trigger puede disparar la activación de otros triggers.
1. Triggers Before (nivel de sentencia)
2. Para cada fila: 1. Trigger Before (a nivel de fila)
2. Ejecuta la Sentencia
3. Triggers After (a nivel de fila)
3. Triggers After (a nivel de sentencia)
Se compromete o se deshace toda la transacción
El orden de ejecución de disparadores del mismo tipo es indeterminado
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Correlation Identifiers Valores OLD y NEW
Tipo especial de variable PL/SQL tratada como un registro de tipo tabla_modificada%ROWTYPE
Con OLD.nombre_columna referenciamos:
al valor que tenía la columna antes del cambio debido a una modificación (UPDATE)
al valor de una columna antes de una operación de borrado sobre la misma (DELETE)
al valor NULL para operaciones de inserción (INSERT)
Con NEW.nombre_columna referenciamos:
Al valor de una nueva columna después de una operación de inserción (INSERT)
Al valor de una columna después de modificarla mediante una sentencia de modificación (UPDATE)
Al valor NULL para una operación de borrado (DELETE)
Condición (WHEN ….) OLD, NEW En el cuerpo del disparador :OLD, :NEW SINTAXIS
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Triggers DML
Disparados por sentencias DML: INSERT, UPDATE o DELETE
Todas las filas o sólo algunas (WHEN)
ISBN GENERO TÍTULO
100-09-89 Novela El Quijote
----- ---- ----
GENERO TOTAL_LIBROS
Novela 50
Infantil 15
CREATE OR REPLACE TRIGGER UpdateEstadisticasGenero
AFTER INSERT OR DELETE OR UPDATE ON Libros
DECLARE
UDDATE Estadisticas SET ….
BEGIN
----------------------
END UpdateEstadisticasGenero;
/
LIBROS ESTADÍSTICAS
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
INSERT INTO EmpleadoDpato VALUES (‘Carlos Gómez', ‘Contabilidad-1’);
ERROR en línea 1: ORA-01779: no se puede modificar una columna que se corresponde con una tabla no reservada por clave
Triggers INSTEAD OF
Sólo sobre VISTAS
DNI NOMBRE DEPARTAMENT
O
11111111 José García CT-1
----- ---- ----
NOMBRE CÓDIGO
Contabilidad - 1 CT-1
Recursos
Humanos
RRHH
EMPLEADO DEPARTAMENTO
CREATE VIEW EmpleadoDpto as
SELECT e.nombre, d.nombre FROM Empleado E, Departamento D
WHERE E.Departamento = D.Codigo;
CREATE OR REPLACE TRIGGER InsertEmepleadoDpto
INSTEAD OF INSERT ON EmpleadoDpto
DECLARE v_cod Departamento.codigo%TYPE;
BEGIN
SELECT codigo INTO v_cod FROM DEPARTAMENTO
WHERE nombre=:NEW.nombre;
INSERT INTO Empleado VALUES … INSERT INTO Departamento VALUES …
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Triggers de Sistema
Disparados por eventos del Sistema o eventos relacionados con las acciones de los Usuarios
Sistema
Arranque y parada
Transacciones
Errores
Usuarios
Login / Logoff
Sentencias DDL: CREATE, ALTER, DROP
CREATE OR REPLACE TRIGGER LogCreations
AFTER CREATE ON SCHEMA
BEGIN
INSERT INTO LogCreates (user_id, object_type,
object_name, object_owner, creation_date)
VALUES (USER, ORA_DCIT_OBJ_TYPE,
ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_OWNER, SYSDATE)
END LogCreations;
/
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Funciones del Cuerpo del Disparador
CREATE OR REPLACE TRIGGER ejemplo
BEFORE INSERT OR UPDATE OR DELETE ON tabla
BEGIN
IF DELETING THEN
Acciones asociadas al borrado
ELSIF INSERTING THEN
Acciones asociadas a la inserción
ELSIF UPDATING(‘COL1’)
Acciones asociadas a la modificación
ELSIF UPDATING(‘COL2’)
Acciones asociadas a la modificación
END IF;
END ejemplo;
/
Inserting, Deleting, Updating
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
RAISE_APPLICATION_ ERROR (nro_error, mensaje); [-20000 y -20999]
CREATE OR REPLACE TRIGGER ejemplo
BEFORE DELETE ON tabla
FOR EACH ROW
BEGIN
IF tabla.columna= valor_no_borrable THEN
RAISE_APPLICATION_ERROR(-20000, ‘La fila no se puede borrar’);
END IF;
...
END ejemplo;
Elevar excepciones en el cuerpo del Disparador
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Declaración de Variables
nombre CONSTANT NUMBER:=valor;
nombre TIPO;
nombre nombretabla.nombrecolumna%TYPE;
nombre nombretabla%ROWTYPE
CREATE...
BEFORE...
[FOR EACH ROW ...]
DECLARE
Declaración de variables
BEGIN
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Activar / Desactivar disparadores
Todos los disparadores asociados a una tabla: ALTER TABLE nombre_tabla ENABLE ALL TRIGGERS
ALTER TABLE nombre_tabla DISABLE ALL TRIGGERS
(Por defecto Todos están Activados al crearse)
Un disparador específico:
ALTER TRIGGER nombre_disparador ENABLE
ALTER TRIGGER nombre_disparador DISABLE
Borrar un Disparador
DROP TRIGGER nombre_disparador;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Consultar información sobre los disparadores
Ver todos los disparadores y su estado SELECT TRIGGER_NAME , STATUS FROM USER_TRIGGERS;
Ver el cuerpo de un disparador SELECT TRIGGER_BODY
FROM USER_TRIGGERS
WHERE TRIGGER_NAME='nombre_disparador';
Ver la descripción de un disparador SELECT DESCRIPTION
FROM USER_TRIGGERS
WHERE TRIGGER_NAME= 'nombre_disparador';
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Ejemplo
CREATE OR REPLACE TRIGGER Reorder
AFTER UPDATE OF Parts_on_hand ON Inventory
FOR EACH ROW
WHEN(new.Parts_on_hand < new.Reorder_point)
DECLARE
x NUMBER;
BEGIN
SELECT COUNT(*) INTO x FROM Pending_orders
WHERE Part_no = :new.Part_no;
IF x = 0 THEN
INSERT INTO Pending_orders
VALUES (:new.Part_no,
:new.Reorder_quantity, sysdate);
END IF;
END;
SELECT Trigger_type, Triggering_event, Table_name
FROM USER_TRIGGERS
WHERE Trigger_name = 'REORDER';
TYPE TRIGGERING_STATEMENT TABLE_NAME
---------------- ------------------------ ----------
AFTER EACH ROW UPDATE INVENTORY
SELECT Trigger_body FROM USER_TRIGGERS
WHERE Trigger_name = 'REORDER';
TRIGGER_BODY
--------------------------------------------
DECLARE
x NUMBER;
BEGIN
SELECT COUNT(*) INTO x
FROM Pending_orders
WHERE Part_no = :new.Part_no;
IF x = 0 THEN
INSERT INTO Pending_orders
VALUES (:new.Part_no, :new.Reorder_quantity, sysdate);
END IF;
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Restricciones: Tablas Mutantes
Tabla mutante (mutating)
Tabla que está siendo modificada por una operación DML
Tabla que se verá afectada por los efectos de un
DELETE CASCADE debido a la integridad referencial (hasta Oracle8i).
Las órdenes del cuerpo de un disparador no pueden:
Leer o actualizar una tabla mutante que esté en la propia declaración del disparador a nivel de fila (ROW TRIGGER)
MUTATING TABLE ERROR RUNTIME ERROR
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Ejemplo
CREATE OR REPLACE TRIGGER trigger_asignaturas
BEFORE INSERT OR UPDATE ON asignaturas
FOR EACH ROW
DECLARE
v_total NUMBER;
v_nombre VARCHAR2(30);
BEGIN
SELECT COUNT(*)
INTO v_total
FROM asignaturas -- ASIGNATURAS está MUTANDO
WHERE DNI = :NEW.DNI;
-- comprueba si el profesor está sobrecargado
IF v_total >= 10 THEN
SELECT nombre||' '||apellidos
INTO v_nombre
FROM profesores
WHERE DNI = :NEW.DNI;
RAISE_APPLICATION_ERROR (-20000, ‘El profesor '||
v_nombre||', está sobrecargado');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001,
‘Datos de profesor incorrectos');
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Ejemplo
UPDATE asignaturas
SET DNI = ‘000000000’
WHERE asignaturas_id = ‘BD’;
UPDATE section
*
ERROR at line 1:
ORA-04091: table BD_XX.ASIGNATURAS is mutating,
trigger/function may not see it
ORA-06512: at "BD_XX.TRIGGER_ASIGNATURAS", line 5
ORA-04088: error during execution of trigger
'BD_XX.TRIGGER_ASIGNATURAS'
SELECT COUNT(*)
INTO v_total
FROM asignaturas
WHERE DNI = :NEW.DNI;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Solución
Crear 2 disparadores
En el disparador a nivel de fila (for each row) almacenamos
los datos que queremos consultar
(los que provocan el error de tabla mutante)
En el disparador a nivel de orden (statement) realizamos la
consulta
(sobre los datos almacenados en lugar de sobre la tabla)
La mejor forma de almacenar los valores es utilizar un paquete
(opcionalmente, podríamos utilizar una tabla)
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Solución
Trigger a nivel de fila:
Guardamos el DNI y el nombre del profesor en 2 variables globales, por tanto CREAMOS UN PAQUETE
CREATE OR REPLACE PACKAGE pck_profesores AS
v_DNI_profesor profesor.DNI%TYPE;
v_nombre_profesor varchar2(50);
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Solución
CREATE OR REPLACE TRIGGER trigger_asignaturas
BEFORE INSERT OR UPDATE ON section
FOR EACH ROW
BEGIN
IF :NEW.DNI IS NOT NULL THEN
BEGIN
pck_profesores.v_DNI_profesor := :NEW.DNI;
SELECT nombre||' '||apellidos
INTO pck_profesores.v_nombre_profesor
FROM profesores
WHERE DNI = pck_profesores.DNI;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,
'This is not a valid instructor');
END;
END IF;
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Solución
TRIGGER a nivel de sentencia realizamos la consulta utilizando las variables globales
CREATE OR REPLACE TRIGGER trigger_asignaturas_statement
AFTER INSERT OR UPDATE ON asignaturas
DECLARE
v_total INTEGER;
BEGIN
SELECT COUNT(*) INTO v_total
FROM asignaturas
WHERE DNI = pck_profesores.v_DNI_profesor;
-- comprobamos si el profesor aludido está sobrecargado
IF v_total >= 10 THEN
RAISE_APPLICATION_ERROR (-20000, 'El profesor, '||
pck_profesores.v_nombre_profesor || ', está sobrecargado’);
END IF;
END;
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Tablas Mutantes Solución
UPDATE asignaturas
SET DNI = ‘000000000’
WHERE asignaturas_id = ‘BD’;
UPDATE asignaturas
*
ERROR at line 1:
ORA-20000: El profesor Carlos Romero está sobrecargado
ORA-06512: at "BD_XX.TRIGGER_ASIGNATURAS_STATEMENT", line 11
ORA-04088: error during execution of trigger
'BD_XX.TRIGGER_ASIGNATURAS_STATEMENT'
Diseño de Bases de Datos y Seguridad de la Información - 2010 www.kybele.urjc.es
Bibliografía
Disparadores en ORACLE
Disparadores
Tema 14