viernes, 29 de mayo de 2015

TRIGGERS en SQL Server - Desencadenadores, disparadores



Son procedimientos asociados a una tabla. Se activan cuando se produce una eliminación, modificación o inserción en la tabla asociada.

Ejemplos de uso de un trigger

Si tenemos una tabla de clientes, y otra de informes, por ej. cada vez que se produzca una baja en clientes, almacenamos el nombre y apellidos del cliente en informe para posteriormente hacer una estadística con los clientes borrados.

Tenemos la tabla de nominas, cada vez que un usuario acceda a ella, el trigger se activa y almacena su nombre en otra tabla.

  • Ejemplo de Trigger para Delete
Vamos a ver un ejemplo de creación de un trigger para cuando borremos un departamento se actualice la tabla empleados borrando todos aquellos cuyo departamento sea el borrado.

En este caso como queremos borrar, obtendremos la información de la tabla Deleted, que es una tabla de sistema donde se almacenan todos los datos borrados de todas las tablas. Con lo que la instrucción quedaría así.

CREATE TRIGGER [BORRADOR]
ON DEPT
FOR DELETE
AS
DELETE FROM EMP
FROM EMP, DELETED
WHERE
EMP.DEPT_NO = DELETED.DEPT_NO

Al ejecutar la eliminación de un departamento llamará al Trigger el cual se ejecutará inmediatamente.

DELETE FROM DEPT WHERE DEPT_NO = 70


  • Ejemplo de Trigger para Insert
En este ejemplo inserto un nuevo empleado y lo que hago es mostrar el empleado insertado desde la tabla inserted.

CREATE TRIGGER
INSERTAR_EMP
ON EMP
FOR INSERT
AS
SELECT * FROM INSERTED

Una vez creado el Trigger realizamos una insercción de datos y obtenemos los siguientes resultado.

INSERT INTO EMP(Emp_No, Apellido, Oficio, Dir, Fecha_Alt, Salario, Comision, Dept_No)
VALUES(7455,'GANOZA','EMPLEADO',7902,'29/05/2015',15520,0,20)



  • Ejemplo de Trigger para Update
Como hemos visto en el ejemplo anterior, para borrar existe la tabla de sistema deleted donde almacena los registros borrados, al igual que para insertar almacena los registros insertados en la tabla inserted, pero para modificar no existe ninguna tabla update, lo que hace en realidad es insertar en la tabla deleted el registro antes de ser modificado y en la tabla inserted el registro ya modificado, porque entiende que se ha borrado un registro y se ha insertado otro.

Con lo cuál para crear un trigger que se active con un update, trabajaremos con las tablas del sistema deleted e inserted.

CREATE TRIGGER [MODIFICAR]
ON DEPT
FOR UPDATE
AS
UPDATE EMP
SET
DEPT_NO = INSERTED.DEPT_NO
FROM EMP,DELETED,INSERTED
WHERE
EMP.DEPT_NO = DELETED.DEPT_NO

Este ejemplo suele funcionar en el caso que se desea actualizar en cascada y no existe una referencia de clave foránea.
Lo que haría es que cuando modificamos el código de un registro en la tabla dept, se activa el trigger, va a la tabla Emp y actualiza los departamento con el nuevo código, que coincidan con el código eliminado. Para ejemplificarlo de algún modo, si actualizo el código del departamento 50 a 80, lo que hace el trigger es buscar en la tabla Emp todos los departamentos con código 50 y actualizarlos a 80.


Super Ejemplo

El siguiente ejemplo almacenará en una tabla que crearemos un registro con datos de cada fila que borremos, modifiquemos o insertemos.
Este registro contendrá
Nº de empleado.
Usuario que realizó la consulta de acción.
Fecha de la consulta de acción.
Tipo de operación realizada.

1) Creamos la tabla

CREATE TABLE Control_BD
(
Emp_no INT NULL,
Usuario VARCHAR(20) NULL,
Fecha DATETIME NULL,
Operacion VARCHAR(15) NULL
)

2) Creamos el trigger de borrado en la tabla de empleados.

CREATE TRIGGER BORRADOR ON EMP
FOR DELETE
AS
INSERT INTO CONTROL_BD (EMP_NO, USUARIO, FECHA, OPERACION)
SELECT EMP_NO,USER_NAME(), GETDATE(),'BORRADO'
FROM DELETED

Siguiendo este ejemplo puedes crear los trigger para insert y update.

Te recomiendo la publicación siguiente de ejercicios con Trigger en donde completamos este ejemplo de control de acciones.


Espero haber ayudado en algo. Hasta la próxima oportunidad!








No hay comentarios:

Publicar un comentario en la entrada