trigger

Disparadores en ORACLE

Diseño de Bases de Datos y Seguridad de
la Información

© 2008 Belén Vela

PL/SQL-1

Disparadores en ORACLE
• PL/SQL: lenguaje de programación
estructurado en bloques
• Bloques: unidad mínima en PL/SQL
– Soportan DML y DDL
– Anónimos / Con nombre

DECLARE ▬ optional
BEGIN ▬ required
EXCEPTION ▬ optional
END; ▬ required
/

• Disparadores (triggers) enOracle: 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
…

© 2008 Belén Vela

CREATE TRIGGER NombreTrigger
BEFORE INSERT ON StarsIn
DECLARE
…..
END;
TRIGGER
/

CREATE PROCEDUREGet_emp_rec
(Emp_number IN
Emp_tab.Empno%TYPE) AS
BEGIN
- - - - END;
PROCEDIMIENTO
/
PL/SQL-2

Disparadores en ORACLE

• 3 Tipos de Disparadores:
– DML (Fila/Sentencia, BEFORE/AFTER)
– INSTEAD OF (vistas)
– SYSTEM
© 2008 Belén Vela

PL/SQL-3

Disparadores en ORACLE
Estructura General de un Disparador
Evento

CREATE [OR REPLACE] TRIGGER nombre
{BEFORE | AFTER | INSTEAD OF}Temporalidad del Evento
{INSERT | DELETE | UPDATE [OF ]} ON
[FOR EACH ROW | STATEMENT]
[WHEN condición]
[DECLARE
…]
BEGIN
cuerpo del trigger
[EXCEPTION
…]
END;
/
© 2008 Belén Vela

Granularidad

Condición

Acción

PL/SQL-4

Disparadores en ORACLE
Estructura General de un Disparador
Ejemplo
INVENTARIO (num_producto, descripción, unidades_dispon, punto_pedido,cantidad_pedido)

PEDIDOSPENDIENTES (num_producto, cantidad_pedido, fecha_pedido)

UPDATE Inventario
SET unidades_dispon=unidades_dispon-3
WHERE num_producto=10456;
SI el número de unidades disponibles de ese producto (num_producto=10456) en
el inventario es inferior al punto de pedido
ENTONCES: se genera una nueva orden de pedido

INSERT INTO PedidosPendientes VALUES (10456, 100, ‘1/01/2009’ );
©2008 Belén Vela

PL/SQL-5

Disparadores en ORACLE
Estructura General de un Disparador
CREATE OR REPLACE TRIGGER Disparador
atributo

tabla

AFTER UPDATE OF unidades_dispon ON Inventario
FOR EACH ROW
WHEN (new.unidades_dispon < new.punto_pedido)

Evento
Condición

Cuando el número de unidades en el inventario sea inferior al punto de pedido.
DECLARE
X NUMBER;
Acción
BEGIN
--Número de pedidos pendientes de ese producto
SELECT COUNT(*) INTO X FROM PedidosPendientes
WHERE num_producto=:new.num_producto;
-- Si no hay ninguna orden de pedido, hacer el pedido
IF x = 0 THEN
INSERT INTO PedidosPendientes
VALUES (:new.num_producto, :new.cantidad_pedido, SYSDATE);
END IF;
© 2008 Belén Vela
END;
PL/SQL-6

Disparadores en ORACLE
Temporalidad del Evento: AFTER /BEFORE
• BEFORE
Ejecutan la acción asociada antes de que la sentencia sea ejecutada
– Decidir si la acción debe realizarse o no
– 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 ….

© 2008Belén Vela

PL/SQL-7

Disparadores en ORACLE
Granuralidad del Evento:
FOR EACH ROW / STATEMENT
• 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 UPDATE ON NombreTabla
FOR EACH ROW
[WHEN …]….
• 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 (incluso si
no hay filas afectadas).
CREATE TRIGGER NombreTrigger
BEFORE INSERT ON NombreTabla
© 2008 Belén Vela
[STATEMENT] . . .
opción por defecto
PL/SQL-8

Disparadores en ORACLE
Ejemplo: A NIVEL DE FILA (ROW TRIGGER)
Cuando se borre en la...