Función de actualización en TSQL trigger

Los disparadores son complicados y necesitas pensar en masa cuando estás creando uno. Un desencadenador se activa una vez por cada instrucción de ACTUALIZACIÓN. Si esa instrucción UPDATE actualiza varias filas, el desencadenador solo se activará una vez. La función UPDATE () devuelve true para una columna cuando esa columna se incluye en la instrucción UPDATE. Esa función ayuda a mejorar la eficiencia de los disparadores al permitirle eludir la lógica SQL cuando esa columna ni siquiera está incluida en la instrucción update. No lo dice si el valor cambió para una columna en una fila dada.

Aquí hay una tabla de ejemplo...

CREATE TABLE tblSample
(
    SampleID INT PRIMARY KEY,
    SampleName VARCHAR(10),
    SampleNameLastChangedDateTime DATETIME,
    Parent_SampleID INT
)

Si se usó el siguiente SQL contra esta tabla:

UPDATE tblSample SET SampleName = 'hello'

..y UN DESPUÉS DE INSERTAR, activador de ACTUALIZACIÓN estaba en efecto, esta instrucción SQL particular siempre evaluaría la función de ACTUALIZACIÓN de la siguiente manera...

IF UPDATE(SampleName) --aways evaluates to TRUE
IF UPDATE(SampleID)  --aways evaluates to FALSE
IF UPDATE(Parent_SampleID) --aways evaluates to FALSE

Tenga en cuenta que UPDATE(SampleName) siempre sería true para esta instrucción SQL, independientemente de cuáles fueran los valores de SampleName antes. Devuelve true porque la ACTUALIZACIÓN la instrucción incluye la columna SampleName en la sección SET de esa cláusula y no se basa en los valores anteriores o posteriores. La función UPDATE() no determinará si los valores han cambiado. Si desea realizar acciones basadas en si se cambian los valores, necesitará usar SQL y comparar las filas insertadas y eliminadas.

Este es un enfoque para mantener sincronizada la última columna actualizada:

--/*
IF OBJECT_ID('dbo.tgr_tblSample_InsertUpdate', 'TR') IS NOT NULL 
  DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
GO
--*/

CREATE TRIGGER dbo.tgr_tblSample_InsertUpdate ON dbo.tblSample
  AFTER INSERT, UPDATE 
AS
BEGIN --Trigger

  IF UPDATE(SampleName)  
    BEGIN
      UPDATE tblSample SET
      SampleNameLastChangedDateTime = CURRENT_TIMESTAMP
      WHERE
        SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))
    END

END --Trigger

La lógica para determinar si se actualizó la fila está en el cláusula anterior. Ese es el verdadero cheque que necesitas hacer. Mi lógica está usando COALESCE para manejar valores NULOS e INSERCIONES.

...
WHERE
  SampleID IN (SELECT Inserted.SampleID 
               FROM Inserted LEFT JOIN Deleted ON Inserted.SampleID = Deleted.SampleID
               WHERE COALESCE(Inserted.SampleName, '') <> COALESCE(Deleted.SampleName, ''))

Tenga en cuenta que la comprobación IF UPDATE() se utiliza para ayudar a mejorar la eficiencia del desencadenador cuando la columna SampleName NO se está actualizando. Si una instrucción SQL actualizó la columna Parent_SampleID, por ejemplo, entonces la comprobación IF UPDATE(SampleName) ayudaría a eludir la lógica más compleja en esa instrucción IF cuando no necesita ejecutarse. Considere usar UPDATE() cuando sea apropiado pero no por la razón equivocada.

También tenga en cuenta que dependiendo de su arquitectura, la función de ACTUALIZACIÓN puede no tener ningún uso para usted. Si su arquitectura de código utiliza un nivel medio que siempre actualiza todas las columnas de una fila de una tabla con los valores en el objeto de negocio cuando se guarda el objeto, la función UPDATE() en un desencadenador se vuelve inútil. En ese caso, es probable que su código siempre actualice todas las columnas con cada declaración de ACTUALIZACIÓN emitida desde el nivel medio. Siendo ese el caso, la función UPDATE(columnname) siempre evaluaría a true cuando se guarden los objetos de negocio porque todos los nombres de columna siempre se incluyen en las instrucciones update. En ese caso, no sería útil usar UPDATE () en el disparador y solo sería una sobrecarga adicional en ese disparador la mayoría del tiempo.

Aquí hay algunos SQL para jugar con el disparador anterior:

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 1, 'One'
UNION SELECT 2, 'Two'
UNION SELECT 3, 'Three'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample

/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
*/

GO

INSERT INTO tblSample
(
  SampleID,
  SampleName
)
SELECT 4, 'Foo'
UNION SELECT 5, 'Five'

GO
SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       One    2010-10-27 14:52:42.567
2       Two    2010-10-27 14:52:42.567
3       Three  2010-10-27 14:52:42.567
4       Foo    2010-10-27 14:52:42.587
5       Five   2010-10-27 14:52:42.587
*/

GO

UPDATE tblSample SET SampleName = 'Foo' 

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample 
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Foo    2010-10-27 14:52:42.657
2       Foo    2010-10-27 14:52:42.657
3       Foo    2010-10-27 14:52:42.657
4       Foo    2010-10-27 14:52:42.587
5       Foo    2010-10-27 14:52:42.657
*/
GO

UPDATE tblSample SET SampleName = 'Not Prime' WHERE SampleID IN (1,4)

SELECT SampleID, SampleName, SampleNameLastChangedDateTime FROM tblSample
/*
SampleID  SampleName SampleNameLastChangedDateTime
----------- ---------- -----------------------------
1       Not Prime  2010-10-27 14:52:42.680
2       Foo        2010-10-27 14:52:42.657
3       Foo        2010-10-27 14:52:42.657
4       Not Prime  2010-10-27 14:52:42.680
5       Foo        2010-10-27 14:52:42.657
*/

--Clean up...
DROP TRIGGER dbo.tgr_tblSample_InsertUpdate
DROP TABLE tblSample

El usuario GBN había sugerido lo siguiente:

IF EXISTS (
    SELECT
        *
    FROM
        INSERTED I
        JOIN
        DELETED D ON I.key = D.key
    WHERE
        D.valuecol <> I.valuecol --watch for NULLs!
    )
   blah

GBN sugerencia de usar un IF (EXISTS (...clause and putting the logic in that IF statement if rows exist that were changed could work. Ese enfoque se activará para TODAS las filas incluidas en el desencadenador, incluso si solo se cambiaron algunas de las filas (lo que puede ser apropiado para su solución, pero también puede no ser apropiado si solo desea hacer algo con las filas donde los valores cambiaron.) Si necesita hacer algo a las filas donde se ha producido un cambio real, necesita una lógica diferente en su SQL que él proporcionó.

En mis ejemplos anteriores, cuando se emite la instrucción UPDATE tblSample SET SampleName = 'Foo' y la cuarta fila ya es 'foo', usando el enfoque de GBN para actualizar una columna "last changed datetime" también actualizaría la cuarta fila, lo que no sería apropiado en este caso.

Estoy de acuerdo en que la mejor manera de determinar si un valor de columna realmente ha cambiado (en lugar de actualizarse con el mismo valor) es hacer una comparación de los valores de columna en las pseudo tablas eliminadas e insertadas. Sin embargo, esto puede ser un verdadero dolor si desea revisar más de unas pocas columnas.

Aquí hay un truco que encontré en algún código que estaba manteniendo (no conozco al autor original): Utilice una UNIÓN y un GRUPO BY con una cláusula HAVING para determinar qué columnas tienen cambiar.

Por ejemplo, en el disparador, para obtener los ID de las filas que han cambiado:

SELECT SampleID
FROM 
    (
        SELECT SampleID, SampleName
        FROM deleted

        -- NOTE: UNION, not UNION ALL.  UNION by itself removes duplicate 
        --  rows.  UNION ALL includes duplicate rows.
        UNION 

        SELECT SampleID, SampleName
        FROM inserted
    ) x
GROUP BY SampleID
HAVING COUNT(*) > 1

Esto es demasiado trabajo cuando solo está comprobando si una sola columna ha cambiado. Pero si usted está comprobando 10 o 20 columnas el método de UNIÓN es mucho menos trabajo que

WHERE COALESCE(Inserted.Column1, '') <> COALESCE(Deleted.Column1, '')
    OR COALESCE(Inserted.Column2, '') <> COALESCE(Deleted.Column2, '')
    OR COALESCE(Inserted.Column3, '') <> COALESCE(Deleted.Column3, '')
    OR ...

Creo que el siguiente código es mejor que los ejemplos anteriores porque se centra solo en las columnas que desea verificar de una manera concisa y eficiente.

Determina si un valor ha cambiado solo en las columnas especificadas. No he investigado su rendimiento en comparación con las otras soluciones, pero está funcionando bien en mi base de datos.

Utiliza el operador EXCEPT set para devolver cualquier fila de la consulta izquierda que no se encuentre también en la consulta derecha. Este código puede se utilizará en los disparadores de INSERCIÓN y ACTUALIZACIÓN.

La columna "PrimaryKeyID" es la clave principal de la tabla (puede ser varias columnas) y es necesaria para permitir la coincidencia entre los dos conjuntos.

-- Only do trigger logic if specific field values change.
IF EXISTS(SELECT  PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM inserted
          EXCEPT
          SELECT PrimaryKeyID
                ,Column1
                ,Column7
                ,Column10
          FROM deleted )    -- Tests for modifications to fields that we are interested in
BEGIN
          -- Put code here that does the work in the trigger

END

Si desea utilizar las filas cambiadas en la lógica de activación posterior, generalmente pongo los resultados de la consulta EXCEPT en una variable de tabla a la que se puede hacer referencia más adelante.

Espero que esto sea de interés: -)

El disparador de actualización se activará en todas las instrucciones de actualización. las filas afectadas están disponibles dentro del desencadenador en las tablas "inserted" y "deleted". Puede comparar los valores antiguos y nuevos comparando las columnas PK en las dos tablas (si tiene un PK). La tabla actual permanece sin cambios hasta que el disparador finalice la ejecución.