SQL Server: Triggers - Robert Lluberes

Un Trigger o desencadenador es un tipo de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento de Lenguaje de Manipulación de Datos (DML), o sea cuando se ejecuta un INSERT, UPDATE o DELETE en una tabla o un evento de Lenguaje de Definición de Datos (DDL) en este caso un DROP TABLE o ALTER TABLE en una base de datos.

La sintaxis básica es la siguiente:

CREATE TRIGGER nombre_trigger
ON { tabla | DATABASE } --Tabla a la que se establecerá el Trigger o la palabra reservada DATABASE para Triggers DDL
{ FOR }{ INSERT, UPDATE, DELETE, DROP_TABLE, ALTER_TABLE} --Evento que se desea lanzar (se puede usar más de uno)
DECLARE @nombre_variable TIPO --Nombre y tipo de datos de la variable si se desea crear
AS Bloque de instrucciones

Ejemplos

Primero crearemos dos tablas para este ejemplo, una contendrá unos pocos campos para almacenar usuarios y otra para dejar rastros de auditoría de esa tabla de usuarios, que es una de las utilidades que se les puede dar a los Triggers.

Tabla Usuarios

CREATE TABLE Usuarios(
IdUsuario INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
Nombres VARCHAR(50),
Edad INT,
Usuario VARCHAR(15),
Contrasena VARCHAR(10)
)

Tabla AuditoriaTblUsuarios

CREATE TABLE AuditoriaTblUsuarios(
IdUsuario INT,
Nombres VARCHAR(50),
Edad INT,
Usuario VARCHAR(15),
Contrasena VARCHAR(10),
UsuarioCreador VARCHAR(100),
Fecha DATETIME
)

Aquí el ejemplo con eventos DML, cada vez que inserte un registro en la tabla Usuarios me copiara este registro a la tabla AuditoriaTblUsuarios y así tener un rastro de que usuario creo ese otro usuario y en qué fecha.

Trigger AuditCreacionUsuarios


CREATE TRIGGER AuditCreacionUsuarios
ON Usuarios --Tabla a asignar el Trigger
FOR INSERT --Evento que se desea lanzar Trigger
AS
--Código a ejecutar cuando se realice un INSERT en la tabla &amp;amp;amp;amp;quot;Usuarios&amp;amp;amp;amp;quot;:
--Declarar variables
DECLARE @IdUsuario INT
DECLARE @Nombres VARCHAR(50)
DECLARE @Edad INT
DECLARE @Usuario VARCHAR(15)
DECLARE @Contrasena VARCHAR(10)
DECLARE @UsuarioCreador VARCHAR(100)
DECLARE @Fecha DATETIME
--Asignar varibles
SET @IdUsuario = (SELECT IdUsuario FROM Inserted)
SET @Nombres = (SELECT Nombres FROM Inserted)
SET @Edad = (SELECT Edad FROM Inserted)
SET @Usuario = (SELECT Usuario FROM Inserted)
SET @Contrasena = (SELECT Contrasena FROM Inserted)
SET @IdUsuario = (SELECT IdUsuario FROM Inserted)
SET @UsuarioCreador = (SELECT system_user)
SET @Fecha = (SELECT GETDATE())

--Insertar en tabla AuditoriaTblUsuarios
INSERT INTO AuditoriaTblUsuarios(IdUsuario, Nombres, Edad, Usuario, Contrasena, UsuarioCreador, Fecha)
VALUES (@IdUsuario, @Nombres, @Edad, @Usuario, @Contrasena, @UsuarioCreador, @Fecha)
GO

La sentencia CREATE TRIGGER es usada para crear el Trigger. La clausula ON indica el nombre de la tabla a cual va a ser aplicado el Trigger. FOR INSERT especifica que es un trigger que se lanza o ejecuta después de insertar. En lugar de FOR INSERT también se puede usar AFTER INSERT.

Seguramente pudieron notar que en el cuerpo del Trigger utilicé la tabla Inserted, esta es una tabla lógica que contiene la fila que ha sido insertada. Lo que hará el trigger es asignar el campo correspondiente a ser insertado en cada variable declarada y posteriormente insertarla en la tabla AuditCreacionUsuarios.

Para probarlo inserté los siguientes datos en la tabla Usuarios:

INSERT INTO Usuarios(Nombres, Edad, Usuario, Contrasena)
VALUES ('Roberto Lluberes',20,'rlluberes','Rlluberes2')
GO

Como pueden observar en la imagen de más abajo la pestaña de resultado me indica que fueron afectadas dos filas, esto es así porque fueron insertados los datos indicados en la tabla Usuarios y a su vez automaticamente el Trigger inserto datos en la tabla AuditoriaTblUsuarios .

Insert-SQL — Resultado de insertar datos en la tabla *Usuarios*

Si consultamos ambas tablas podemos verificar que los datos se insertaron correctamente, lo que indica que el Trigger AuditCreacionUsuarios se ejecutó correctamente.

Insert-SQL-Resultado — Resultado del Trigger AuditCreacionUsuarios

Ejemplo con eventos DDL, crearemos un Trigger que pueda prevenir el borrado accidental de una tabla en la base de datos.

CREATE TRIGGER SeguridadBorrarTabla
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Para borrar esta tabla debes deshabilitar el Trigger &amp;amp;amp;amp;quot;SeguridadBorrarTabla&amp;amp;amp;amp;quot;'
ROLLBACK

Para verificar que el Trigger SeguridadBorrarTabla esta funcionando correctamente intente borrar la tabla AuditCreacionUsuarios y como podemos ver en la imagen de más abajo no fue posible, esto quiere decir que el Trigger está funcionando.

Trigger-DDL — Resultado de la prueba del Trigger *SeguridadBorrarTabla*

Finalmente podemos ver los Trigger creados desde el Explorador de Objetos de SQL, en la siguiente ruta NombreTabla > Triggers para los triggers DML y para los Triggers DDL en NombreBaseDeDatos > Programmability > Database Triggers, desde ahí podemos modificarlo y/o deshabilitarlo.