Base Datos Avanzado II
Triggers(Disparadores)
ING. RICARDO CARLOS INQUILLA QUISPE
Objetivos de la sesión

Definir los triggers.

Entender las diferentes modalidades.

Aplicar triggers para resolver
problemas reales.
Temas a Tratar



Creación de disparadores.
Componentes de un disparador.
Disparadores de BD.
Trigger
Bloque PL/SQL que se ejecuta
automáticamente cuando se realiza un
evento (INSERT, UPDATE, DELETE).
 Almacenado en la BD.
 No puede ser local.
 No acepta parámetros

Utilidad del Trigger

Restricción de integridad. (consultar si hay
saldo antes de comprar)

Auditoria de información. (almacenar quién
modificó ciertos registros: sueldo).

Aviso automático a otros módulos PL/SQL
para que realicen cierta acción. (inactivar al
trabajador, eliminar el permiso a todos los
módulos del sistema)
Resumen
Sintaxis del Trigger
CREATE OR REPLACE TRIGGER nombre
BEFORE INSERT OR UPDATE
ON tabla
FOR EACH ROW WHEN (condición)
DECLARE
....
BEGIN
....
END;
INSERT
BEFORE
AFTER
UPDATE
DELETE
OF col1, col2, …, colN
Tipos de Disparadores
Orden
INSERT, UPDATE, DELETE
 Temporización
BEFORE, AFTER
 Nivel
FOR EACH ROW

Uso de Identificadores de Correlación
:old y :new

Permiten acceder a los datos de la fila procesada en el
trigger.
ORDEN
:old
:new
INSERT
NULL
Valores a
insertar
UPDATE
Valores a
eliminar
Valores a
insertar
DELETE
Valores a
eliminar
NULL
Ejercicio 1

Crear 2 tablas temporal y product.
CREATE TABLE
temporal_sec_gru
( prodid number(6),
descrip char(30) );
CREATE TABLE producto_sec_gru
( prodid number(6),
descrip char(30));
INSERTAR 3
REGISTROS
Ejercicio 1

Crear un trigger que se dispare una vez
por cada fila insertada.
CREATE OR REPLACE TRIGGER t_sec_gru
AFTER INSERT
ON temporal_sec_gru
FOR EACH ROW
BEGIN
dbms_output.put_line('mensaje repetido');
END;
Ejercicio 2

Crear un trigger que se dispare sólo una
vez luego de la inserción.
CREATE OR REPLACE TRIGGER t_una_vez_sec_gru
AFTER INSERT
ON temporal_sec_gru
BEGIN
dbms_output.put_line(‘una sola vez');
END;
Ejercicio 2

Probar ambos triggers al insertar más de
un registro a la vez en la tabla temporal.
INSERT INTO temporal_sec_gru
SELECT *
FROM
producto_sec_gru ;
Ejercicio 3

Crear una tabla de auditoría para la tabla
product
CREATE TABLE productcib
( prodid number(6),
descrip char(30));
CREATE TABLE product_auditcib
( prodid number(6),
descrip char(30),
user_audit varchar2(20) );

Crear un trigger que almacene los datos
insertados en la tabla product y el nombre
del usuario que realizó la inserción.
CREATE OR REPLACE TRIGGER t_audit_product_22
AFTER INSERT
ON productcib
FOR EACH ROW
DECLARE
vusu varchar2(20);
BEGIN
select user into vusu from dual;
INSERT INTO product_auditcib
VALUES (:new.prodid, :new.descrip, vusu);
END;
Ejercicio 3

Inserte un registro en la tabla product
INSERT INTO PRODUCTcib
VALUES (5, 'PROBANDO TRIGGER');

Compruebe que se insertó el registro en la
tabla product_audit.
SELECT *
FROM product_auditcib;
Eliminar o Deshabilitar

Eliminar un trigger
DROP TRIGGER nombre;

Deshabilitar un trigger
ALTER TRIGGER nombre DISABLE;

Habilitar un trigger
ALTER TRIGGER nombre ENABLE;
RESUMEN
Ejercicio 3

Crear una tabla historial para almacenar
las modificaciones a la tabla product.
CREATE TABLE historial_cib
( prodid number(6),
descrip char(30),
fecha date );
Ejercicio 3

Crear un trigger que almacene el historial
de modificaciones de nombres de
productos.
CREATE OR REPLACE TRIGGER t_product
AFTER UPDATE
ON productcib
FOR EACH ROW
BEGIN
INSERT INTO historial_cib VALUES
(:old.prodid, :old.descrip, sysdate);
END;
Ejercicio 3

Actualizar una descripción de la tabla
product.
UPDATE productcib
SET descrip = 'NUEVA DESCRIPCIÓN'
WHERE prodid = 5 ;
Ejercicio 3

Comprobar que haya insertado en la tabla
historial.
SELECT *
FROM historial_cib ;
Cláusula WHEN
Válida sólo cuando se usa FOR EACH
ROW.
 Se disparará sólo cuando cumpla la
condición.
 Se puede usar las variables old y new
dentro de la condición, pero no se usan
los dos puntos (:)

Ejercicio 4
 Crear
un trigger que muestre un mensaje
cuando se inserta un empleado con
salario mayor a 1000.
CREATE OR REPLACE TRIGGER t_emp
AFTER INSERT
ON emp
FOR EACH ROW WHEN (new.sal>1000)
BEGIN
dbms_output.put_line('Salario superior');
END;
Creamos la tabla Empleado
Ejercicio 4

Insertar un empleado con salario mayor a
1000.
INSERT INTO emp (empno, sal)
VALUES (9876, 1001);

Insertar un empleado con salario no
mayor a 1000.
INSERT INTO emp (empno, sal)
VALUES (9877, 1000);
RESUMEN
Predicados en Triggers
PREDICADO COMPORTAMIENTO
INSERTING
TRUE si fue un INSERT.
FALSE caso contrario.
UPDATING
TRUE si fue un UPDATE.
FALSE caso contrario.
DELETING
TRUE si fue un DELETE.
FALSE caso contrario.
Ejercicio 5
Modificar la tabla historial para aumentar
un campo ORDEN.
 En este campo se almacenará el tipo de
orden DML que genera el registro.

ALTER TABLE historial_cib
ADD orden varchar2(10);
Ejercicio 5

Modificar el trigger t_product para que
almacene el historial de inserciones y
eliminaciones, además del de
modificaciones de productos.
Ejercicio 5
CREATE OR REPLACE TRIGGER t_product
AFTER INSERT OR UPDATE OR DELETE
ON productcib
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO historial_cib VALUES(:new.prodid, :new.descrip,
sysdate, 'INSERT');
ELSIF UPDATING THEN
INSERT INTO historial_cib VALUES(:old.prodid, :old.descrip,
sysdate, 'UPDATE');
ELSIF DELETING THEN
INSERT INTO historial_cib VALUES(:old.prodid, :old.descrip,
sysdate, 'DELETE');
END IF;
END;
Probamos el Triggers

INSERT INTO PRODUCTcib VALUES (6,
'PROBANDO TRIGGER 2');

UPDATE productcib SET descrip = 'NUEVA
DESCRIPCIÓN 2' WHERE prodid = 5 ;

DELETE FROM productcib WHERE prodid = 5 ;
CONSULTAR DATOS TRIGGER
Ejercicios
Cree un trigger que valide que cualquier
actualización del salario de un empleado el
monto no debe pasar de 10000 nuevos
soles. En caso se esté actualizando el
salario de un empleado con un monto
mayor, el trigger debe asignar el monto
10000 como tope.
 Cree un trigger que capture información
de los empleados a los cuales se les está
modificando el salario. Solamente para los
de contabilidad (ACCOUNTING).

Ejercicios
Simule una eliminación en cascada de tal
forma que cuando se elimine una orden se
eliminen los items de dicha orden.
 Cree tablas de auditoria(una sola tabla)
para las modificaciones en las tablas emp,
ord, item, deberá almacenar la fecha ,
pc,usuario ,valor antiguo(ord) y
nuevo(ord) y el nombre de la tabla en la
cual se realizo el cambio

FIN DE CLASE
MUCHAS GRACIAS
,ALGUNA PREGUNTA?
introducción
PARTES BÁSICAS
Descargar

Triggers