Trigger SQL Server



¿Que es un trigger?

Un trigger (disparador) es un objeto que se encuentra ligado a una tabla el cual se ejecuta después de un evento que sucede en la tabla, las cuales son: INSERT, UPDATE, DELETE. Esto significa que al suceder la acción en la tabla automáticamente se ejecuta el trigger el cual puede tener una o varias instrucciones a proceder.

Ventajas

  • Provee una alternativa para la verificación de la integridad en los datos.
  • Puede detectar errores en la lógica de negocio en la base de datos.
  • Se puede utilizar como tareas programadas (Task Schedule).
  • Útiles para auditar los cambios en las bases de datos.

Desventajas

  • Pueden aumentar la sobrecarga del servidor de base de datos.
  • Se ejecutan y son invisibles desde las aplicaciones cliente, por lo tanto, es difícil averiguar qué sucede en la capa de base de datos.

Sintaxis de creación, modificación y eliminado de triggers.

Crear:

CREATE TRIGGER <Nombre>
ON <NombreTabla>
AFTER <Evento>
AS

Modificar:

ALTER TRIGGER <Nombre>
ON <NombreTabla>
AFTER <Evento>
AS

Borrar:

DROP TRIGGER <Nombre>

Tablas de eventos en triggers


Al crear los triggers se ejecutan después de afectar los eventos en las tablas, SQL Server crear tablas por evento para indicar cuales fueron los valores que se utilizaron a ejecutar el trigger y podemos acceder a estos en las tablas: INSERTED, DELETED, al actualizar la tabla (UPDATE), los datos se encuentran en la tabla INSERTED.

Ejemplos:

Query para el siguiente ejemplo, tabla Personas e historial de nombre.

CREATE TABLE Personas(
       PersId int PRIMARY KEY IDENTITY(1,1) NOT NULL,
       PersNombre nvarchar (80) NOT NULL,
       PersFecNacimiento datetime NULL,
       PersActivo bit NOT NULL
       )

CREATE TABLE HistorialNombresPersonas
(
Id Int IDENTITY Primary Key
PersId INT,
ValorAnterior Nvarchar(80),
ValorNuevo Nvarchar(80)
)

Para el siguiente ejemplo, se va almacenar todos los cambios realizados en la columna nombre de la tabla Personas.

CREATE TRIGGER TR_HistoNombre
ON dbo.Personas
AFTER UPDATE
AS
       DECLARE @Id INT
       SET @Id = (SELECT PersId FROM INSERTED)

       DECLARE @NombreNuevo NVARCHAR(80)
       SET @NombreNuevo = (SELECT PersNombre FROM INSERTED)

       DECLARE @NombreAnterior NVARCHAR(80)
       SET @NombreAnterior = (SELECT PersNombre FROM DELETED)

       INSERT INTO HistorialNombresPersonas
       VALUES (@Id,@NombreAnterior,@NombreNuevo)

El trigger anterior funciona correctamente sí nos aseguramos que siempre se va a actualizar registro por registro. Las tablas INSERTED y DELETED pueden contener más de una fila, al ejecutar el UPDATE en más de un registro en la tabla; sobre el trigger ocurre la excepción y los cambios no se ven afectados. Ejemplo de query que provoca el error al actualizar en la tabla:

UPDATE Personas SET
       PersNombre = 'Nuevo Nombre'


ALTER TRIGGER TR_HistoNombre
ON Personas
AFTER UPDATE
AS
       INSERT INTO HistorialNombresPersonas
       SELECT INSERTED.PersId, DELETED.PersNombre, INSERTED.PersNombre
       FROM INSERTED
       INNER JOIN DELETED ON INSERTED.PersId = DELETED.PersId

Modificación del trigger para actualizar más de una fila a la vez, al ejecutarse inserta los nombres correctamente en la tabla HistorialNombresPersonas, aun así, este trigger se ejecuta al actualizar cualquier columna de la tabla Persona, en este ejemplo puede funcionar, pero estaría acumulando los nombres cuando realmente no sufrió un cambio. Ejemplo sí en la tabla Persona se actualiza el campo PersFecNacimiento, se dispara el trigger y almacena en la tabla HistorialNombresPersonas y procede a guardar un registro con el mismo nombre en los campos ValorAnterior y ValorNuevo.

En la siguiente alteración del trigger se corrige el problema anterior validando dentro de la operación del trigger que, si la columna PersNombre es la afectada, se procede a crear el registro en la tabla HistorialNombresPersonas.

ALTER TRIGGER TR_HistoNombre
ON Personas
AFTER UPDATE
AS
       IF UPDATE (PersNombre)
       BEGIN
              INSERT INTO HistorialNombresPersonas
              SELECT INSERTED.PersId, DELETED.PersNombre, INSERTED.PersNombre
              FROM INSERTED
              INNER JOIN DELETED ON INSERTED.PersId = DELETED.PersId
       END

Hasta esta alteración del trigger funciona correctamente, pero aún se tiene un detalle que el cual queda a criterio de cómo se quiere manejar. El detalle pasa cuando se actualiza la columna PersNombre con el mismo valor que tiene anteriormente.

UPDATE Personas SET
       PersNombre = PersNombre
       WHERE Personas.PersId = 1

Este query actualiza la columna PersNombre con el mismo valor, el cual genera un registro en la tabla HistorialNombresPersonas, esto pasa porque cumple con todos los filtros que tiene el trigger, sí se requiere guardar un registro con el mismo valor en las columnas, se debe agregar un filtro para que no se genere el registro.

ALTER TRIGGER TR_HistoNombre
ON Personas
AFTER UPDATE
AS
       IF UPDATE (PersNombre)
       BEGIN
              INSERT INTO HistorialNombresPersonas
              SELECT INSERTED.PersId, DELETED.PersNombre, INSERTED.PersNombre
              FROM INSERTED
              INNER JOIN DELETED ON INSERTED.PersId = DELETED.PersId
              WHERE DELETED.PersNombre <> INSERTED.PersNombre
       END

Con el filtro WHERE queda listo para que no genere registro con el mismo valor en ambas columnas.
Sigue este Blog desde Telegram: Clic aquí para unirte al canal

Compartir
Compartir
Compartir

Comments

Post a Comment

Week's Hit

Jobs SQL Server P1

Image
¿Que es un Jobs en SQL Server? Son tareas programadas para ejecutar operaciones de usuarios como Querys, respaldo de bases de datos, ejecutar análisis de bases de datos, etc. Ambiente Los Jobs se ejecutan bajo el servicio del SQL Agent , si este servicio no se encuentra en ejecución, no se podrá crearlos, ni ejecutarse en casa de tener creados. Para revisar si el servicio se encuentra en ejecución, en el servidor o equipo que se encuentre instalado la instancia de SQL, buscamos la aplicación SQL Server Configuration . Al abrir la configuración de SQL, se verifica si el servicio con nombre: SQL Server Agent ( <NombreInstancia> ) se encuentre en ejecución, en caso de no estarlo, se inicia seleccionando el servicio y haciendo clic en iniciar (star). Nota : Esta funcionalidad solo está disponible para las versiones licenciadas de SQL Server, esta función no se encuentra disponible en SQL Server Express, no se podrá iniciar SQL Server Agent . Una vez
Read more

Funciones SQL Server

Image
SQL Server nos proporciona una numerosa cantidad de funciones integradas del sistema, el cual en esta ocasión hablaré sólo de algunas, además de estas funciones, SQL permite crear funciones de usuario, las cuales podemos crear consultando tablas de nuestra base de datos e incluso combinando con funciones del sistema u otras funciones de usuario. A considerar al implementar Funciones. Las funciones no pueden ejecutar Procedimientos Almacenados (S.P.). Solo se puede anidar hasta 32 funciones al momento de ejecutar. No pueden insertar datos en otra tabla con la instrucción INTO ejemplo. INSERT INTO Tabla2        SELECT * FROM Tabla No se puede formatear el resultado en XML. En las funciones tipo tablas, el resultado no se puede regresar con la instrucción ORDER BY en el RETURN. Solo las tablas temporales como variables están permitidas dentro de las funciones. Sintaxis de creación, modificación y eliminado de las funciones. Crear:         CREAT
Read more