Por: Daniel Farina | Actualizado: 2019-02-26 | Comentarios (7) | Relacionado: Más > Triggers
Problema
Ya has aprendido a escribirSQL queries ystored procedures, pero ahora quieres aprender sobreSQL Server triggers. Este consejo le servirá de punto de partiday de guía para crear triggers de SQL Server.
Solución
Los triggers son uno de los temas más incomprendidos por la gente nueva en SQL Server.Tal vez esto se deba al hecho de que permiten casi toda la misma funcionalidad que los procedimientos almacenados, lo que hace que el desarrollador inexperto esté confundido sobre si crear un procedimiento almacenado o un trigger.
¿Qué es un trigger de SQL Server?
Un trigger de SQL Server es una pieza de código de procedimiento, como un procedimiento almacenado que sólo se ejecuta cuando ocurre un evento determinado. Hay diferentes tipos de eventos que pueden disparar un trigger. Sólo por nombrar algunos, la inserción de filas en una tabla, un cambio en la estructura de una tabla e incluso un usuario que inicia sesión en una instancia de SQL Server.
Hay tres características principales que hacen que los disparadores sean diferentes de los procedimientos almacenados:
- Los disparadores no pueden ser ejecutados manualmente por el usuario.
- No hay posibilidad de que los triggers reciban parámetros.
- No se puede cometer o revertir una transacción dentro de un trigger.
- Triggers DDL (Data Definition Language). Esta clase de triggers se dispara ante eventos que cambian la estructura (como la creación, modificación o eliminación de una tabla), o en ciertos eventos relacionados con el servidor como los cambios de seguridad o los eventos de actualización de estadísticas.
- Triggers DML (Data Modification Language). Esta es la clase de triggers más utilizada. En este caso, el evento de disparo es una sentencia de modificación de datos; puede ser una sentencia de inserción, actualización o eliminación, ya sea en una tabla o en una vista.
- FOR o AFTER : Estos tipos de triggers se ejecutan después de que la sentencia de disparo finalice (ya sea una inserción, actualización o eliminación).
- INSTEAD OF : A diferencia del tipo FOR (AFTER), los triggersINSTEAD OF se ejecutan en lugar de la sentencia de disparo. En otras palabras, este tipo de disparador sustituye a la sentencia de disparo. Esto es muy útil en los casos en los que se necesita tener integridad referencial entre bases de datos.
- Si te ha resultado difícil entender la forma en que he capturado qué operación DML se ha realizado en la tabla Empleados echa un vistazo a este consejo:Entender las tablas insertadas y eliminadas de SQL Server para los disparadores DML.
- Para un ejemplo de un trigger INSTEAD OF puede consultar este consejo:Using INSTEAD OF triggers in SQL Server for DML operations.
- Puede encontrar más información sobre el DMV sys.dm_exec_sessions aquí:Understanding and Using sys.dm_exec_sessions in SQL Server.
- Para una explicación más detallada del comando EXECUTE AS eche un vistazo al siguiente consejo:Conceder permiso con el comando EXECUTE AS en SQL Server.
- Manténgase en sintonía con la categoría de consejos sobre activadores de SQL Server para obtener más consejos y trucos.
- Más consejos para desarrolladores de bases de datos…
El hecho de que sea imposible utilizar parámetros en los triggers no es una limitación para recibir información del evento de disparo. Como se verá más adelante, existen alternativas para obtener información sobre el evento que se dispara.
Clases de triggers de SQL Server
Existen dos clases de triggers en SQL Server:
Además, los triggers DML tienen diferentes tipos:
¿Cuál es la importancia de los triggers de SQL Server?
Una de las características fundamentales de las bases de datos relacionales es la consistencia de los datos.Esto significa que la información almacenada en la base de datos debe ser consistente en todo momento para cada sesión y cada transacción. La forma en que los motores de bases de datos relacionales como SQL Server implementan esto es aplicando restricciones como claves primarias y claves externas. Pero a veces eso no es suficiente.
En SQL Server no hay posibilidad de aplicar la integridad referencial entre dos tablas utilizando claves externas si esas tablas están en diferentes bases de datos o diferentes servidores. En ese caso la única forma de implementarla es mediante el uso de triggers.
¿Cómo puedo saber qué filas se han actualizado, insertado o eliminado mediante un TriggerDML de SQL Server?
En el caso de los triggers DML, durante la ejecución del trigger hay dos tablas virtuales que contienen los datos que están siendo afectados por la ejecución del trigger. Esas tablas se denominan inserted y deleted y tienen la misma estructura de tabla que su tabla base. Hay que tener en cuenta que las tablas insertadas y eliminadas no siempre están disponibles juntas (es decir, se puede tener la tabla insertada, pero no la eliminada o viceversa). Encontrará más información sobre estas tablas en el siguiente consejoEntender las tablas insertadas y eliminadas de SQL Server para los activadores DML.
Sintaxis de activadores DML de SQL Server
En la siguiente sección de código, verá la sintaxis básica de CREATE TRIGGER.
CREATE TRIGGER trigger_name ON { Table name or view name } { FOR | AFTER | INSTEAD OF } { , , }
Además, la siguiente tabla describe cada uno de los argumentos de la sintaxis de CREATE TRIGGER.
Argumento | Descripción |
---|---|
Con <Opciones> | En este argumento puedes especificar opciones adicionales para la creación del trigger. Esto lo trataré más adelante. |
POR | DESPUÉS DE | Indica cuándo debe dispararse el trigger cuando ocurre un evento determinado, como un evento de inserción, actualización o borrado. |
, , | El evento DML (o lista de eventos) que hará que se dispare el disparador. |
Opción con | Descripción | Observaciones |
---|---|---|
ENCRYPTION | Encripta el código del Trigger. | No funciona con tablas de memoria optimizada |
EXECUTE AS | Cambia el contexto de seguridad sobre el que se ejecutará el trigger | Requerido para triggers sobre tablas de memoria optimizada. |
COMPILACIÓN_NATIVA | Compila el código del trigger en un binario para que se ejecute de forma nativa. | Requerido para triggers en tablas optimizadas para memoria. |
SCHEMABINDING | Asegura que las tablas a las que hace referencia un trigger no puedan ser eliminadas o alteradas. | Requerido para triggers en tablas de memoria optimizada. |
Escenarios de uso de triggers de SQL Server
Hay dos escenarios claros en los que los triggers son la mejor opción: la auditoría y la aplicación de reglas de negocio. Mediante el uso de un desencadenante, puede realizar un seguimiento de los cambios en una tabla determinada escribiendo un registro de registro con información sobre quién realizó el cambio y qué se modificó en la tabla.
Tal vez piense que puede hacer lo mismo en la aplicación con un procedimiento almacenadoque maneja la modificación de datos como inserciones y actualizaciones. Usted puede utilizar un procedimiento almacenado, pero en tal caso no será capaz de registrar los cambios que se hicieron directamente a la base de datos desde fuera de la aplicación.
Lo mismo ocurre cuando se quiere hacer cumplir las reglas de negocio con un procedimiento almacenado.Si alguien modifica los datos de la tabla base desde fuera de la aplicación puede tener un problema porque la consistencia de los datos no se puede garantizar. Para evitar este problema, se aseguraría de que el procedimiento almacenado fuera la única forma de acceder a la tabla.
Ejemplo de Trigger DML de SQL Server
Supongamos que tenemos una base de datos para el departamento de recursos humanos. Esta base de datos contiene una tabla Empleados para mantener la información del personal y los salarios.Con la ayuda de un trigger podemos mantener un registro de auditoría en una tabla separada que contenga cada modificación del registro, así como el usuario que hizo el cambio y la hora en que se produjo.
En primer lugar, debemos crear la tabla Empleados.
CREATE TABLE Employees ( EmployeeID integer NOT NULL IDENTITY(1, 1) , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) PRIMARY KEY CLUSTERED (EmployeeID) )GO
A continuación, tenemos que crear la tabla EmpleadosAuditoría para almacenar los registros de auditoría. Esta tabla tiene la misma estructura que la tabla Empleados, pero incluye una columna AuditId como clave primaria, ModifiedDate para guardar la fecha de modificación, ModifiedBy para saber quién modificó la tabla Empleados y finalmente Operation donde indicaremos la operación DML que generó el registro de auditoría con una de las tres letras (I parainsertar, U para Actualizar y D para Eliminar).
CREATE TABLE EmployeesAudit ( AuditID INTEGER NOT NULL IDENTITY(1, 1) , EmployeeID INTEGER , EmployeeName VARCHAR(50) , EmployeeAddress VARCHAR(50) , MonthSalary NUMERIC(10, 2) , ModifiedBy VARCHAR(128) , ModifiedDate DATETIME , Operation CHAR(1) PRIMARY KEY CLUSTERED ( AuditID ) )GO
Para poder probar el trigger de ejemplo necesitamos añadir algunos datos a la tablaEmpleados.
INSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary )SELECT 'Mark Smith', 'Ocean Dr 1234', 10000UNION ALLSELECT 'Joe Wright', 'Evergreen 1234', 10000UNION ALLSELECT 'John Doe', 'International Dr 1234', 10000UNION ALLSELECT 'Peter Rodriguez', '74 Street 1234', 10000GO
Ahora que tenemos el entorno de pruebas configurado, es el momento de crear nuestro trigger. Echa un vistazo al código de abajo.
CREATE TRIGGER TR_Audit_Employees ON dbo.Employees FOR INSERT, UPDATE, DELETEAS DECLARE @login_name VARCHAR(128) SELECT @login_name = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID IF EXISTS ( SELECT 0 FROM Deleted ) BEGIN IF EXISTS ( SELECT 0 FROM Inserted ) BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'U' FROM Deleted D END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT D.EmployeeID , D.EmployeeName , D.EmployeeAddress , D.MonthSalary , @login_name , GETDATE() , 'D' FROM Deleted D END END ELSE BEGIN INSERT INTO dbo.EmployeesAudit ( EmployeeID , EmployeeName , EmployeeAddress , MonthSalary , ModifiedBy , ModifiedDate , Operation ) SELECT I.EmployeeID , I.EmployeeName , I.EmployeeAddress , I.MonthSalary , @login_name , GETDATE() , 'I' FROM Inserted I ENDGO
Básicamente el código consiste en obtener el usuario que está modificando el Employeestable buscando en el sys.dm_exec_sessions Dynamic ManagementView la sesión con el SPID actual. Después, el trigger inserta un registro en la tabla EmployeesAudit por cada registro insertado, actualizado o eliminado en la tablaEmployees, así como la hora actual y la operación DML que disparó eltrigger.
Para probar el trigger, he creado tres consultas. Puse el código dentro de una transacción sólo para mantener el orden en mi entorno de prueba, puede omitir esto.
La primera de esas consultas es una actualización.
BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 UPDATE EmployeesSET EmployeeName = 'zzz'WHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION
En la siguiente captura de pantalla verás el registro actualizado en la tabla Empleados y el nuevo registro en EmpleadosAuditoría que lleva la cuenta de la operación DML sobre la tabla Empleados.
La segunda consulta es una inserción de dos filas en la tabla Empleados.
BEGIN TRANSACTIONINSERT INTO dbo.Employees ( EmployeeName , EmployeeAddress , MonthSalary ) SELECT 'zz' , 'dsda' , 10000 UNION ALL SELECT 'Markus Rubius' , 'dsda' , 6000SELECT *FROM dbo.EmployeesSELECT *FROM dbo.EmployeesAuditROLLBACK TRANSACTION
En la siguiente captura de pantalla verá las dos filas insertadas en la tabla Empleados y su respectivo registro de auditoría en la tabla EmpleadosAuditoría.
Por último, la tercera consulta es una sentencia de borrado en la tabla Empleados.
BEGIN TRANSACTIONSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1DELETE FROM dbo.EmployeesWHERE EmployeeID = 1 SELECT *FROM dbo.EmployeesAuditSELECT *FROM dbo.EmployeesWHERE EmployeeID = 1ROLLBACK TRANSACTION
En la siguiente captura de pantalla verás la fila eliminada de la tabla Empleados y su respectivo registro de auditoría en la tabla EmpleadosAuditoría.
Siguientes pasos
- Última actualización: 2019-02-26
.
Acerca del autor
Ver todos mis consejos
0 comentarios