Triggers

 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.

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

1. Trigger que evita que se borren en la tabla categorias mas de un registro, el Trigger detecta que se borra mas de un registro y dispara un error. (Ver Manejo de errores)

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  un mensaje definido por el usuario almacenado en la vista de catálogo  sys.messages o puede generar un mensaje dinámicamente.

2.  Visualizar los triggers de la base de datos

select  *  from  sys.triggers
go

3. 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.

4. Borrar una sola categoria

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

Trigger de Inserción

5. 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
PRINT ‘El Nombre de la Categoria ya existe…’
END
ELSE
PRINT ‘Categoría ingresada a la Base de datos’
go

6. 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. (Ver Triggers Instead Of)

6.  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