Triggers en SQL Server

 Triggers

Un desencadenador o Trigger es una clase de procedimiento almacenado que se ejecuta automáticamente cuando se realiza una transacción en la bases de datos. En este artículo explicamos los tipos de Triggers existentes y desarrollamos ejemplos de Triggers DML.

Tipos de Triggers

Existen los siguientes tipos de Triggers

  1. Los Triggers DML se ejecutan cuando se realizan operaciones de manipulación de datos (DML). Los eventos DML son instrucciones INSERT, UPDATE o DELETE realizados en una tabla o vista.
  2. Los Triggers DDL se ejecutan al realizar eventos de lenguaje de definición de datos (DDL). Estos eventos corresponden a instrucciones CREATE, ALTER y DROP.
  3. Los Triggers Logon, que se disparan al ejecutarse un inicio de sesión en SQL Server.

Consideraciones

  • Una tabla puede tener un máximo de tres triggers: uno de actualización, uno de inserción y uno de eliminación.
  • Cada trigger puede aplicarse a una sola tabla o vista. Por otro lado, un mismo trigger se puede aplicar a las tres acciones: UPDATE, INSERT y DELETE.
  • No se puede crear un trigger en una vista ni en una tabla temporal, pero el trigger puede hacer referencia a estos objetos.
  • Los trigger no se permiten en las tablas del sistema.

Las tablas Inserted y Deleted

Son tablas especiales que tienen la misma estructura de las tablas que han desencadenado la ejecución del trigger.
La tabla Inserted está disponible en las operaciones INSERT y UPDATE.
La tabla Deleted está disponible en las operaciones UPDATE y DELETE.

Note que para una operación de actualización, las dos tablas pueden ser utilizadas.

Para crear un Trigger DML se utiliza:

CREATE [ OR ALTER ] TRIGGER [ Esquema . ]NombreTrigger
ON { tabla | vista }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
Begin
Instrucciones T-SQL
End

Para crear un Trigger DDL se utiliza:

CREATE [ OR ALTER ] TRIGGER NombreTrigger
ON { ALL SERVER | BaseDatos }
{ FOR | AFTER } { TipoEvento | GrupoEventos } [ ,…n ]
AS
Begin
Instrucciones T-SQL
End

Ejercicios

Usando Northwind
use Northwind
go

Trigger que evita que se borren en la tabla categorias mas de un registro
Create Trigger trEliminaSoloUnaCategoria
on Categories for delete
As
— Se cuentan cuantos registros se eliminaron
IF (Select COUNT(*) from Deleted)>1
BEGIN
Raiserror(‘NO PUEDE ELIMINAR MÁS DE UN REGISTRO’,16,1)
Rollback Tran
END
go

Raiserror    Raiserror(‘Mensaje’,Severidad,Estado)
Genera un mensaje de error e inicia el procesamiento de errores  de la sesión. RAISERROR puede hacer referencia a un mensaje
definido por el usuario almacenado en la vista de catálogo  sys.messages o puede generar un mensaje dinámicamente.

Para visualizar los triggers de la base de datos
select * from sys.triggers
go

Para probar si el Trigger creado trEliminaSoloUnaCategoria funciona
se insertarán dos categorías con nombre Pisos y Carros
Insertar Categorias
insert into Categories (CategoryName) values (‘Pisos’),(‘Carros’)
go
Eliminar una categoria
delete Categories where CategoryID = 10
go
Se elimina sin inconvenientes porque es una sola categoría.

Al eliminar varias categorias el Trigger se dispara y no lo permite.
delete Categories where CategoryID in (12,13,15)
go

Mens 50000, Nivel 16, Estado 1, Procedimiento trEliminaSoloUnaCategoria, Línea 62
NO PUEDE ELIMINAR MÁS DE UN REGISTRO
Mens. 3609, Nivel 16, Estado 1, Línea 56
La transacción terminó en el desencadenador. Se anuló el lote.

Borrar una sola categoria
delete Categories where CategoryID = 17
go
La categoría se borra con éxito

Trigger de Inserción
Crear un Trigger que permita comprobar que se inserta una categoría con nombre diferente.
La tabla Inserted será utilizada para comprobar si ya hay una categoría con el mismo nombre que la insertada.

Create trigger trCategoriaInsertaSinRepetidos ON Categories For Insert
AS
IF (Select COUNT (*) From Inserted, Categories
WHERE Inserted.CategoryName = Categories.CategoryName) >1
BEGIN
Rollback Transaction
return -1
PRINT ‘El Nombre de la Categoria ya existe…’
END
ELSE
return 0
PRINT ‘Categoría ingresada a la Base de datos’
go

Insertar categoría con nombre repetido: Pisos
insert into Categories (CategoryName) values (‘Pisos’)
go
El Nombre de la Categoria ya existe…
Mens. 3609, Nivel 16, Estado 1, Línea 87
La transacción terminó en el desencadenador. Se anuló el lote.

 Trigger Instead Of

Realizar acciones después de las instrucciones  de un procedimiento o las escritas directamente  por el usuario.

Por ejemplo, copiar los datos a una vista de Clientes al insertar uno en la tabla Customers

Primero se crea la vista con los clientes.
Create View ClientesVista as
Select CustomerID,CompanyName,Address,City,Phone from Customers
go

Al agregar un Cliente en Customers se desea que se inserte en ClientesVista
Crear un Trigger Instead of para la tabla Customers.

Create trigger trClienteInsertaVista ON Customers Instead of Insert
AS
BEGIN
Insert Into ClientesVista
SELECT CustomerID, CompanyName, Address, City, Phone
FROM Inserted
Print ‘Insertado correctamente en la vista’
END
go

Insertar un cliente en Customers
insert into Customers (CustomerID, CompanyName,ContactName,
ContactTitle,Address, City, Phone)
Values (‘TQ884’,’Trainer SQL Pro ‘,’SQL Professional’,
‘Gerente General’,’Av. Larco 94994′,’España’,’209834534′)
go

Visualizar la vista para comprobar que el cliente ha sido insertado.
select * from ClientesVista where CustomerID like ‘T%’
go