Post on 29-Jan-2016
ING. RICARDO CARLOS INQUILLA QUISPEING. RICARDO CARLOS INQUILLA QUISPE
Triggers(Disparadores)Triggers(Disparadores)
Base Datos Avanzado IIBase Datos Avanzado II
Objetivos de la Objetivos de la sesiónsesión
Definir los triggers.Definir los triggers.
Entender las diferentes modalidades.Entender las diferentes modalidades.
Aplicar triggers para resolver Aplicar triggers para resolver
problemas reales.problemas reales.
Temas a TratarTemas a Tratar
Creación de disparadores.Creación de disparadores. Componentes de un disparador.Componentes de un disparador. Disparadores de BD.Disparadores de BD.
TriggerTriggerBloque PL/SQL que se ejecuta Bloque PL/SQL que se ejecuta
automáticamente cuando se realiza automáticamente cuando se realiza un evento (INSERT, UPDATE, un evento (INSERT, UPDATE, DELETE).DELETE).
Almacenado en la BD.Almacenado en la BD.No puede ser local.No puede ser local.No acepta parámetrosNo acepta parámetros
Utilidad del TriggerUtilidad del Trigger
Restricción de integridad.Restricción de integridad. (consultar si (consultar si hay saldo antes de comprar)hay saldo antes de comprar)
Auditoria de información.Auditoria de información. (almacenar (almacenar quién modificó ciertos registros: sueldo).quién modificó ciertos registros: sueldo).
Aviso automático a otros módulos Aviso automático a otros módulos PL/SQL para que realicen cierta PL/SQL para que realicen cierta acción.acción. (inactivar al trabajador, eliminar (inactivar al trabajador, eliminar el permiso a todos los módulos del el permiso a todos los módulos del sistema)sistema)
ResumeResumenn
Sintaxis del TriggerSintaxis del TriggerCREATE OR REPLACE TRIGGER nombre BEFORE INSERTON tablaFOR EACH ROW WHEN (condición)DECLARE
....BEGIN
....END;
CREATE OR REPLACE TRIGGER nombre BEFORE INSERTON tablaFOR EACH ROW WHEN (condición)DECLARE
....BEGIN
....END;
OF col1, col2, …, colNBEFORE
AFTER
INSERT
UPDATE
DELETE
OR UPDATE
Tipos de DisparadoresTipos de Disparadores
OrdenOrden
INSERT, UPDATE, DELETEINSERT, UPDATE, DELETETemporizaciónTemporización
BEFORE, AFTERBEFORE, AFTERNivelNivel
FOR EACH ROWFOR EACH ROW
Uso de Identificadores de Uso de Identificadores de CorrelaciónCorrelación :old y :new :old y :new
Permiten acceder a los datos de la fila Permiten acceder a los datos de la fila procesada en el trigger.procesada en el trigger.
ORDENORDEN :old:old :new:new
INSERTINSERT NULLNULL Valores a Valores a insertarinsertar
UPDATEUPDATE Valores a Valores a eliminareliminar
Valores a Valores a insertarinsertar
DELETEDELETE Valores a Valores a eliminareliminar
NULLNULL
Ejercicio 1Ejercicio 1
Crear 2 tablas temporal y product.Crear 2 tablas temporal y product.
CREATE TABLE temporal_sec_gru
( prodid number(6),
descrip char(30) );
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 1Ejercicio 1
Crear un trigger que se dispare una Crear un trigger que se dispare una vez vez por cada fila insertadapor 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;
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 2Ejercicio 2
Crear un trigger que se dispare Crear un trigger que se dispare sólo sólo una vezuna vez luego de la inserción. 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;
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 2Ejercicio 2Probar ambos triggers al insertar Probar ambos triggers al insertar
más de un registro a la vez en la más de un registro a la vez en la tabla temporal.tabla temporal.
INSERT INTO temporal_sec_gruSELECT *FROM producto_sec_gru ;
INSERT INTO temporal_sec_gruSELECT *FROM producto_sec_gru ;
Ejercicio 3Ejercicio 3
CREATE TABLE product_auditcib
( prodid number(6),
descrip char(30),
user_audit varchar2(20) );
CREATE TABLE product_auditcib
( prodid number(6),
descrip char(30),
user_audit varchar2(20) );
Crear una tabla de auditoría para la Crear una tabla de auditoría para la tabla producttabla product
CREATE TABLE productcib( prodid number(6), descrip char(30));
CREATE OR REPLACE TRIGGER t_audit_product_22AFTER INSERTON productcib
FOR EACH ROWDECLARE vusu varchar2(20);BEGIN select user into vusu from dual; INSERT INTO product_auditcibVALUES (:new.prodid, :new.descrip, vusu);END;
CREATE OR REPLACE TRIGGER t_audit_product_22AFTER INSERTON productcib
FOR EACH ROWDECLARE vusu varchar2(20);BEGIN select user into vusu from dual; INSERT INTO product_auditcibVALUES (:new.prodid, :new.descrip, vusu);END;
Crear un trigger que almacene los datos Crear un trigger que almacene los datos insertados en la tabla product y el nombre insertados en la tabla product y el nombre del usuario que realizó la inserción.del usuario que realizó la inserción.
INSERT INTO PRODUCTcib VALUES (5, 'PROBANDO TRIGGER');INSERT INTO PRODUCTcib VALUES (5, 'PROBANDO TRIGGER');
Inserte un registro en la tabla productInserte un registro en la tabla product
SELECT * FROM product_auditcib;SELECT * FROM product_auditcib;
Compruebe que se insertó el registro Compruebe que se insertó el registro en la tabla product_audit.en la tabla product_audit.
Ejercicio 3Ejercicio 3
Eliminar o DeshabilitarEliminar o Deshabilitar
Eliminar un triggerEliminar un trigger
DROP TRIGGER nombre;DROP TRIGGER nombre;
ALTER TRIGGER nombre DISABLE;ALTER TRIGGER nombre DISABLE;
Deshabilitar un triggerDeshabilitar un trigger
ALTER TRIGGER nombre ENABLE;ALTER TRIGGER nombre ENABLE;
Habilitar un triggerHabilitar un trigger
RESUMENRESUMEN
Ejercicio 3Ejercicio 3
Crear una tabla historial para Crear una tabla historial para almacenar las modificaciones a la almacenar las modificaciones a la tabla product.tabla product.CREATE TABLE historial_cib
( prodid number(6),
descrip char(30),
fecha date );
CREATE TABLE historial_cib
( prodid number(6),
descrip char(30),
fecha date );
Ejercicio 3Ejercicio 3Crear un trigger que almacene el Crear un trigger que almacene el
historial de modificaciones de historial de modificaciones de nombres de productos.nombres de productos.
CREATE OR REPLACE TRIGGER t_productAFTER UPDATEON productcibFOR EACH ROWBEGIN INSERT INTO historial_cib VALUES (:old.prodid, :old.descrip, sysdate);END;
CREATE OR REPLACE TRIGGER t_productAFTER UPDATEON productcibFOR EACH ROWBEGIN INSERT INTO historial_cib VALUES (:old.prodid, :old.descrip, sysdate);END;
Ejercicio 3Ejercicio 3Actualizar una descripción de la tabla Actualizar una descripción de la tabla
product.product.
UPDATE productcib SET descrip = 'NUEVA DESCRIPCIÓN'WHERE prodid = 5 ;
UPDATE productcib SET descrip = 'NUEVA DESCRIPCIÓN'WHERE prodid = 5 ;
Ejercicio 3Ejercicio 3Comprobar que haya insertado en la Comprobar que haya insertado en la
tabla historial.tabla historial.
SELECT *FROM historial_cib ;SELECT *FROM historial_cib ;
Cláusula WHENCláusula WHENVálida sólo cuando se usa FOR EACH Válida sólo cuando se usa FOR EACH
ROW.ROW.Se disparará sólo cuando cumpla la Se disparará sólo cuando cumpla la
condición.condición.Se puede usar las variables old y new Se puede usar las variables old y new
dentro de la condición, pero no se dentro de la condición, pero no se usan los dos puntos (:)usan los dos puntos (:)
Ejercicio 4Ejercicio 4
CREATE OR REPLACE TRIGGER t_empAFTER INSERTON emp FOR EACH ROW WHEN (new.sal>1000)BEGIN dbms_output.put_line('Salario superior');END;
CREATE OR REPLACE TRIGGER t_empAFTER INSERTON emp FOR EACH ROW WHEN (new.sal>1000)BEGIN dbms_output.put_line('Salario superior');END;
Crear un trigger que muestre un Crear un trigger que muestre un mensaje cuando se inserta un mensaje cuando se inserta un empleado con salario mayor a 1000.empleado con salario mayor a 1000.
Creamos la tabla EmpleadoCreamos la tabla Empleado
Ejercicio 4Ejercicio 4
INSERT INTO emp (empno, sal)VALUES (9876, 1001);INSERT INTO emp (empno, sal)VALUES (9876, 1001);
Insertar un empleado con salario Insertar un empleado con salario mayor a 1000.mayor a 1000.
INSERT INTO emp (empno, sal)VALUES (9877, 1000);INSERT INTO emp (empno, sal)VALUES (9877, 1000);
Insertar un empleado con salario no Insertar un empleado con salario no mayor a 1000.mayor a 1000.
RESUMENRESUMEN
Predicados en TriggersPredicados en Triggers
PREDICADPREDICADOO
COMPORTAMIENTOCOMPORTAMIENTO
INSERTINGINSERTING TRUE si fue un INSERT.TRUE si fue un INSERT.
FALSE caso contrario.FALSE caso contrario.
UPDATINGUPDATING TRUE si fue un UPDATE.TRUE si fue un UPDATE.
FALSE caso contrario.FALSE caso contrario.
DELETINGDELETING TRUE si fue un DELETE.TRUE si fue un DELETE.
FALSE caso contrario.FALSE caso contrario.
Ejercicio 5Ejercicio 5
Modificar la tabla historial para Modificar la tabla historial para aumentar un campo ORDEN.aumentar un campo ORDEN.
En este campo se almacenará el tipo En este campo se almacenará el tipo de orden DML que genera el registro.de orden DML que genera el registro.
ALTER TABLE historial_cib
ADD orden varchar2(10);
ALTER TABLE historial_cib
ADD orden varchar2(10);
Ejercicio 5Ejercicio 5Modificar el trigger t_product para Modificar el trigger t_product para
que almacene el historial de que almacene el historial de inserciones y eliminaciones, además inserciones y eliminaciones, además del de modificaciones de productos.del de modificaciones de productos.
Ejercicio 5Ejercicio 5CREATE OR REPLACE TRIGGER t_productAFTER INSERT OR UPDATE OR DELETEON productcibFOR EACH ROWBEGIN 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;
CREATE OR REPLACE TRIGGER t_productAFTER INSERT OR UPDATE OR DELETEON productcibFOR EACH ROWBEGIN 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 TriggersProbamos el Triggers
INSERT INTO PRODUCTcib VALUES (6, INSERT INTO PRODUCTcib VALUES (6, 'PROBANDO TRIGGER 2');'PROBANDO TRIGGER 2');
UPDATE productcib SET descrip = 'NUEVA UPDATE productcib SET descrip = 'NUEVA DESCRIPCIÓN 2' WHERE prodid = 5 ;DESCRIPCIÓN 2' WHERE prodid = 5 ;
DELETE FROM productcib WHERE prodid = 5 DELETE FROM productcib WHERE prodid = 5 ;;
CONSULTAR DATOS TRIGGERCONSULTAR DATOS TRIGGER
EjerciciosEjerciciosCree un trigger que valide que cualquier Cree un trigger que valide que cualquier
actualización del salario de un actualización del salario de un empleado el monto no debe pasar de empleado el monto no debe pasar de 10000 nuevos soles. En caso se esté 10000 nuevos soles. En caso se esté actualizando el salario de un empleado actualizando el salario de un empleado con un monto mayor, el trigger debe con un monto mayor, el trigger debe asignar el monto 10000 como tope.asignar el monto 10000 como tope.
Cree un trigger que capture información Cree un trigger que capture información de los empleados a los cuales se les de los empleados a los cuales se les está modificando el salario. Solamente está modificando el salario. Solamente para los de contabilidad (ACCOUNTING).para los de contabilidad (ACCOUNTING).
EjerciciosEjerciciosSimule una eliminación en cascada de Simule una eliminación en cascada de
tal forma que cuando se elimine una tal forma que cuando se elimine una orden se eliminen los items de dicha orden se eliminen los items de dicha orden.orden.
Cree tablas de auditoria(una sola tabla) Cree tablas de auditoria(una sola tabla) para las modificaciones en las tablas para las modificaciones en las tablas emp, ord, item, deberá almacenar la emp, ord, item, deberá almacenar la fecha , pc,usuario ,valor antiguo(ord) y fecha , pc,usuario ,valor antiguo(ord) y nuevo(ord) y el nombre de la tabla en nuevo(ord) y el nombre de la tabla en la cual se realizo el cambiola cual se realizo el cambio
FIN DE CLASEFIN DE CLASE
MUCHAS MUCHAS GRACIAS ,ALGUNA GRACIAS ,ALGUNA PREGUNTA?PREGUNTA?
introducciónintroducción
PARTES BÁSICASPARTES BÁSICAS