Triggers DML en SQL Server

Como ya habíamos visto en el artículo sobre Triggers en MySQL, un Trigger es una rutina especial que se ejecuta automáticamente cuando surgen eventos DML en nuestras tablas. Quiere decir que no podemos ejecutarlos explícitamente como a los procedimientos o funciones, si no que lo dejamos en manos del gestor de bases de datos.
Recuerda que los eventos DML surgen al usar las sentencias INSERT, UPDATE y DELETE. En SQL Server un Trigger se ejecuta por una operación completa de modificación, no por cada fila de la operación como lo hace MySQL.

Como creo un Trigger en SQL Server?

Es sencillo, usaremos el comando CREATE TRIGGER con la siguiente sintaxis:CREATE TRIGGER nombre_triggerON { tabla|vista }
[ WITH ENCRYPTION ]
{ AFTER | INSTEAD OF }{ INSERT, UPDATE, DELETE }
[ NOT FOR REPLICATION ]
AS
Bloque de instrucciones

A continuación veremos la utilidad de cada parte de la definicion anterior:
WITH ENCRYPTION:
Encripta el código del Trigger para que no pueda ser interpretado por nadie mas.AFTER:Indica que el Trigger se ejecutará después de que las operaciones DML se hayan ejecutado correctamente. Esta clausula no se aplica en las vistas.INSTEAD OF:Permite ejecutar el Trigger en vez de la operación DML,es decir, SQL Server ignora dicha operación para ejecutar al Trigger. Ten en cuenta que solo debe existir un Trigger tipo INSTEAD OF para cada operación DML.INSERT, UPDATE, DELETE:En este apartado eliges que sentencias DML estará asociadas a tu Trigger. Puedes realizar la combinación que desees. Ten en cuenta que no se permite establecer una actualización o eliminación que vaya a ser en cascada.NOT FOR REPLICATION:
Evita que el Trigger se ejecute cuando una operación de replicación vaya a alterar nuestra tabla asociada.

Los Triggers INSTEAD OF no son lo mismo que los BEFORE de MySQL. Los primeros reemplazan una operación DML y los segundos se ejecutan antes de esta, pero no la reemplazan.Podrías explicar algún ejemplo de un Trigger AFTER?

Claro que si!, a continuación ilustraremos con sencillez un pequeño Trigger que imprima un mensaje después de que se haya ejecutado una inserción en una tabla que guarda los Pacientes de un hospital.CREATE TRIGGER dbo.paciente_AI_Trigger ON dbo.PACIENTE
AFTER INSERT
AS
PRINT "Se modificaron la siguiente cantidad de filas";

Luego que este creado el Trigger insertamos una fila en la tabla PACIENTE:INSERT INTO PACIENTE (IDPACIENTE,NOMBRE, APELLIDO,ENFERMEDAD)
VALUES (1001,"Robin","Vallegaz","Tuberculosis");

Este sería el resultado:

Que pasaría si en el ejemplo anterior usamos INSTEAD OF?

Modifiquemos el código y veamos:CREATE TRIGGER dbo.paciente_AI_Trigger ONdbo.PACIENTE
INSTEAD OF INSERT
AS
PRINT "Se modificaron la siguiente cantidad de filas";

Ahora tratemos de insertar un paciente:INSERT INTO PACIENTE (IDPACIENTE,NOMBRE, APELLIDO,ENFERMEDAD)
VALUES (1002,"Carlos","Guerra","Polio");

Resultado:


Aparentemente se acaba de agregar una fila, pero no es así, el Trigger se ha ejecutado y ha evitado que se agregue. Esto se comprueba realizando un SELECT al código 1002 con que supuestamente insertarmos le registro.SELECT NOMBRE
FROM PACIENTE
WHERE IDPACIENTE = 1002;



Al consultar por la existencia del paciente vemos que este no ha sido insertado. El Trigger cumplió su cometido.Usa el procedimiento sp_helptrigger para ver que procedimientos están asociados a una tabla particular.

Como relaciono las columnas de la tabla con el Trigger?

Usaremos las tablas auxiliares inserted y deleted que guardan temporalmente los registros insertados y los que han sido eliminados. Esta conveniencia es similar a los identificadores NEW y OLD de MySQL.
Cuando sea necesario en el Trigger realizaremos consultas sobre estas dos tablas y así obtener consistencia en nuestros requerimientos o reglas de negocio. También es posible saber que columna alteraste usando la función UPDATE(nombre_columna), la cual devuelve el valor de TRUE si la columna indicada fue modificada dento del Trigger.Que utilidades tienen los Triggers?

Sus usos son variados y dependen de tus necesidades. Por lo general los Triggers se usan para Validar si los datos a insertar en un tabla tienen las características correctas, Mantener reglas de negocio, Realizar seguimientos de las modificaciones de una tabla(logs), Proteger la base de datos de alteraciones inseguras y muchas aplicaciones mas.
Suponga que tiene una base de datos que maneja el Sistema de facturación de su Tienda de Caramelos y que quiere saber en que momentos se han realizado modificaciones en la tabla FACTURA.
Auditoría de una tabla
Construiremos un Trigger para la sentencia UPDATE que guarde aquellos cambios realizados en una tabla llamada FACTURA_LOGS.CREATE TRIGGER FACTURA_UPDATE_TRIGGER ONFACTURA
AFTER UPDATE
AS
BEGIN
DECLARE @DESCVARCHAR(500) = "Se modificó el valor de ";
IF UPDATE(IDFACTURA)
SELECT @DESC =@DESC + "IDFACTURA de "+D.IDFACTURA+" a "+I.IDFACTURA+", "
FROM INSERTED AS I,DELETED AS D;
IF UPDATE(IDCLIENTE)
SELECT @DESC=@DESC + "IDCLIENTE de "+D.IDCLIENTE+" a "+I.IDCLIENTE+", "
FROM INSERTED AS I,DELETED AS D;
IF UPDATE(FECHA)
SELECT @DESC=@DESC + "FECHA de "+CAST(D.FECHA  AS VARCHAR) +" a "
+CAST(I.FECHA AS VARCHAR)+", "
FROM INSERTED AS I,DELETED AS D;
IF UPDATE(DESCUENTO)SELECT @DESC=@DESC + "DESCUENTO de "
+CAST(D.DESCUENTO AS VARCHAR)+" a "+CAST(I.DESCUENTO AS VARCHAR)+", "
FROM INSERTED AS I,DELETED AS D;
IF UPDATE(TOTAL)
SELECT @DESC=@DESC + "TOTAL de "+CAST(D.TOTAL AS VARCHAR)+" a "
+CAST(I.TOTAL AS VARCHAR)
FROM INSERTED AS I,DELETED AS D;
INSERT INTO FACTURA_LOGS
VALUES (@DESC);
END;

El código anterior es ejecutado cuando se modifique la tabla FACTURA. Con al función UPDATE() comprobamos que campos han sido alterados. Registramos los cambios de un valor anterior al valor actual consultando las tablas INSERTED y DELETED. Finalmente insertamos en nuestra tabla FACTURA_LOGS el varchar final creado como descripción.
Regla de negocio simple
En la base de datos de una Empresa petrolera existe una tabla llamada CANDIDATO. Esta entidad hace referencia a las personas que por méritos se han destacado entre varios aspirantes para ocupar una serie de vacantes que existen.
Si por alguna razón se intenta insertar un candidato con menos de 18 años, este registro debe ser remitido automáticamente a una tabla llamada CPENDIENTE. Esto con el fin de evaluar cada caso específico para el futuro.
Para ello emplearemos un Trigger que valide el atributo edad. Observa:CREATE TRIGGER candidato_insert_trigger ONCANDIDATO
AFTER INSERT
AS
BEGIN
DELETE FROM CANDIDATO
WHERE EDAD<18;
INSERT INTO CPENDIENTE
SELECT * FROM INSERTED WHERE EDAD<18;
END;

Es sencillo, solo borramos de la tabla CANDIDATO aquellos clientes con menos de 18 años y luego los insertamos en CPENDIENTE.Como elimino un TRIGGER de la base de datos?

Con la sentencia DROP TRIGGER, déjame mostrarte como eliminamos un trigger asociado a una base de datos perteneciente a una Entidad financiera.DROP TRIGGER Finanzas.balance_update_trigger;

También puedes inhabilitar el Trigger para que no sea ejecutado. Esto evita que lo borremos y poder tenerlo disponible para el futuro. Para deshabilitarlo o habilitarlo usaremos los comandos DISABLE o ENABLE en una sentencia ALTER TABLE. Veamos un ejemplo para desactivar un Trigger:ALTER TABLE empleado DISABLE TRIGGER empleado_trigger;

Con esa instrucción estamos desactivando el trigger empleado_trigger relacionado con la tabla EMPLEADO. Si deseas activarlo simplemente usas ENABLE y listo.James Revelo Urrea - Desarrollador independiente http://www.hermosaprogramacion.com