Triggers en Transact SQL

Los trigger, comúnmente conocidos como desencadenadores o disparadores, son un tipo especial de procedimientos almacenados que se ejecutan automáticamente cuando se produce un evento específico en la base de datos.

La naturaleza de estos eventos puede ser DML (INSERT, UPDATE, DELETE), o DDL(CREATE, ALTER, DROP)

Los trigger por tanto, se utilizan a menudo para automatizar procesos en la base de datos que se ejecutarán bajo circunstancias definidas.

CREATE TRIGGER

La sintaxis básica para la creación y definición de un trigger DML es la siguiente:

CREATE TRIGGER nombreTrigger
ON tabla|vista
[ WITH <opciones> ]
{ FOR | AFTER | INSTEAD OF }
{ INSERT|UPDATE|DELETE}
AS [codigo]

La sintaxis básica para la creación y definición de un trigger DDL es la siguiente:

CREATE TRIGGER nombreTrigger
ON ALL SERVER | DATABASE
[ WITH <opciones> ]
{ FOR | AFTER }
{ CREATE... | ALTER ...| DROP ...}
AS [codigo]

• La cláusula FOR o AFTER especifica que el trigger solo se ejecute después de que todas las operaciones de la instrucción SQL desencadenadora se inicien correctamente. Esta cláusula no podrá ser utilizada en trigger que operen sobre vistas.

• La cláusula INSTEAD OF, especifica que el trigger se desencadene en vez de la la instrucción SQL desencadenadora. Esta cláusula no podrá ser utilizada en trigger que operen sobre desencadenadores DDL o LOGON.

A continuación, veremos algunos ejemplos para ilustrar el funcionamiento de los trigger o desencadenadores.

TRIGGER DML AFTER

El siguiente trigger, se encargará de actualizar la cuenta de un cliente a NULL cuando se realice cualquier modificación en dicho registro:

CREATE TRIGGER triggerCuenta
    ON Clientes AFTER UPDATE
    AS
      BEGIN
        DECLARE @idClientes INT
        SELECT  @idClientes = Clientes.idClientes
        FROM Clientes
        INNER JOIN inserted
        ON Clientes.idClientes = inserted.idClientes;
        UPDATE Clientes
        SET cuenta = NULL
        WHERE idClientes = @idClientes
      END
 GO

• Lo primero que hacemos internamente en el trigger, es recuperar el idClientes específico del registro sobre el que va actuar el disparador. Existen para ellos unas tablas temporales específicas en el uso de los desencadenadores que son: inserted y deleted.

• Una vez recuperado el idClientes específico, realizamos un UPDATE de la cuenta a NULL para dicho cliente

A continuación, procedemos a realizar una modificación de un registro para comprobar que se ejecuta el disparador correctamente:

UPDATE Clientes
SET direccion = 'C/ Virgen del rosal 33'
WHERE idClientes = 1;

Consultamos el registro y comprobamos que al modificar la direccion, la cuenta se ha actualizado a NULL por efecto del trigger:

SELECT * FROM Clientes
WHERE idClientes = 1;

TRIGGER DML INSTEAD OFF

El siguiente trigger, tiene como finalidad devolver un mensaje cuando nos disponemos a eliminar cualquier registro de la tabla Clientes:

CREATE TRIGGER triggerDeleteCliente
    ON Clientes INSTEAD OF DELETE
    AS
     PRINT 'Se va a proceder a eliminar un registro de la tabla Clientes'

Para probar que se ejecuta el disparador, procedemos a eliminar cualquier registro de la tabla Clientes:

 DELETE FROM Clientes
 WHERE nombre ='Jorge'
 AND apellidos = 'Luso Otero';

Observaremos que en la consola mostrará el mensaje especificado en el trigger:

TRIGGER DDL

El siguiente trigger, tiene como finalidad devolver un mensaje siempre que se elimine o se modifique una tabla.

 CREATE TRIGGER ddlTable
    ON DATABASE
    FOR  DROP_TABLE, ALTER_TABLE
    AS
     PRINT 'Se ha realizado una modificación u eliminación estructural permanente de una tabla'

Para probar que se ejecuta el disparador, procedemos a eliminar de nuestra base de datos la tabla existente Empleados:

 DROP TABLE IF EXISTS Empleados;

Observaremos que en la consola mostrará el mensaje especificado en el trigger:

ALTER TRIGGER

ALTER TRIGGER es la instrucción para modificar trigger ya declarados.

Vamos a proceder a modificar el trigger INSTEAD OF: triggerDeleteCliente, ya que atendiendo a las reglas de integridad referencial, si un idClientes está presente como Foreign Key en la tabla Pedidos, no puede ser eliminado de la tabla Clientes.

La modificación que haremos, por tanto, consistirá en recuperar en una variable el idClientes y eliminar el registro asociado primero en la tabla Pedidos, para posteriormente eliminarlo en la tabla Clientes y mostrar el mensaje definido en el trigger.

 ALTER TRIGGER triggerDeleteCliente
    ON Clientes INSTEAD OF DELETE
    AS
        BEGIN
            DECLARE @idClientes INT
            SELECT  @idClientes = Clientes.idClientes
            FROM Clientes
            INNER JOIN deleted
            ON Clientes.idClientes = deleted.idClientes;

            DELETE FROM Pedidos
            WHERE idClientes = @idClientes
            DELETE FROM Clientes
            WHERE idClientes = @idClientes

            PRINT 'Se va a proceder a eliminar un registro de la tabla Clientes (y de la tabla Pedidos si está presente)'
        END

DROP TRIGGER

La sentencia DROP TRIGGER permite eliminar completamente trigger generados dentro de SQL Server.

DROP TRIGGER IF EXISTS triggerCuenta;

Como en otros tipos de DROP, es recomendable utilizar siempre la cláusula IF EXISTS