w3schools - Función de actualización en el disparador TSQL

select case sql ejemplos

Question

Tengo una pregunta sobre la actualización de la función TSQL. Por ejemplo, tengo una tabla con un campo Nombre. Si compruebo si el Nombre del campo está cambiado o no en un desencadenante Después de la actualización le gusta esto:

 if Update(Name)
Begin
-- process
End

¿La actualización seguirá siendo VERDADERO incluso si el nombre no se cambia? La siguiente declaración de actualización lo actualizará con el mismo valor:

 SELECT @v_Name = Name From MyTable Where Id = 1;
Update MyTable Set Name = @v_Name where Id = 1;

Si Update () devuelve TRUE, incluso si el valor de Name no se cambia, ¿tengo que comparar el valor en las tablas virtuales insertadas y eliminadas para averiguar si realmente se cambió el valor?

Por cierto, las tablas insertadas y eliminadas son tablas virtuales y pueden contener más de una fila de datos si una instrucción TSQL INSERT o UPDATE modifica más de una fila de datos. En el caso de más de un registro, ¿el número de recuento de filas en las tablas virtuales insertadas y eliminadas es el mismo y cuál es el significado real de Actualizar (Nombre) como VERDADERO? ¿Significa que al menos uno ha cambiado? ¿O significa que Update (Name) significa que el campo de Name ha sido establecido por la instrucción Update independientemente de si el valor ha cambiado?

El servidor SQL que uso es Microsoft SQL 2005.

Answer 1

El activador de actualización se activará en todas las declaraciones de actualización. las filas impactadas están disponibles dentro del activador en las tablas "insertado" y "eliminado". Puede comparar los valores antiguos y nuevos comparando las columnas PK en las dos tablas (si tiene una PK). La tabla actual permanece sin cambios hasta que el gatillo finaliza la ejecución.

Answer 2

Acepto 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 verificar más de unas pocas columnas.

Este es un truco que encontré en algún código que mantenía (no conozco al autor original): use un UNION y un GROUP BY con una cláusula HAVING para determinar qué columnas han cambiado.

por ejemplo, en el desencadenante, para obtener las identificaciones 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 está revisando 10 o 20 columnas, el método UNION 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 ...

Answer 3

UPDATE() puede ser verdadero, incluso si tiene el mismo valor. No confiaría personalmente en él y compararía valores.

Segundo, DELETED e INSERTED tienen el mismo número de filas.

La función Update () no es por fila, sino en todas las filas. Otra razón para no usarlo.

Más aquí en MSDN , sin embargo, es un poco escaso, realmente.

Después de comentar:

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

Answer 4

Los desencadenantes son complicados y debes pensar a granel cuando crees uno. Un disparador dispara una vez por cada instrucción UPDATE. Si esa instrucción UPDATE actualiza varias filas, el disparador solo se disparará una vez. La función UPDATE () devuelve verdadero para una columna cuando esa columna se incluye en la instrucción UPDATE. Esa función ayuda a mejorar la eficacia de los desencadenadores al permitirle esquivar la lógica de SQL cuando esa columna ni siquiera está incluida en la declaración de actualización. No le dice si el valor cambió para una columna en una fila determinada.

Aquí hay una tabla de muestra ...

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

Si se utilizó el siguiente SQL en esta tabla:

UPDATE tblSample SET SampleName = ''hello''

... y un desencadenador DESPUÉS DE INSERTAR, ACTUALIZADO estaba en vigor, esta instrucción SQL particular siempre evaluaría la función ACTUALIZAR 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á verdadero para esta instrucción SQL, independientemente de los valores de SampleName anteriores. Devuelve verdadero porque la instrucción UPDATE incluye la columna SampleName en la sección SET de esa cláusula y no se basa en lo que eran los valores antes o después. La función UPDATE () no determinará si los valores han cambiado. Si desea realizar acciones en función de si se cambian los valores, necesitará usar SQL y comparar las filas insertadas y eliminadas.

Aquí hay un enfoque para mantener sincronizada una ú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 la fila se actualizó está en la cláusula WHERE anterior. Ese es el verdadero control que debes hacer. Mi lógica es usar COALESCE para manejar valores NULL e INSERTS.

...
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 verificación IF UPDATE () se usa para ayudar a mejorar la eficacia del desencadenante cuando la columna SampleName NO se está actualizando. Si una instrucción SQL actualizó la columna Parent_SampleID por ejemplo, esa verificación IF UPDATE (SampleName) ayudaría a eludir la lógica más compleja en esa declaración IF cuando no necesita ejecutarse. Considere usar UPDATE () cuando sea apropiado, pero no por el motivo equivocado.

Además, tenga en cuenta que, según su arquitectura, la función ACTUALIZAR puede no ser útil para usted. Si su arquitectura de código usa un nivel intermedio que siempre actualiza todas las columnas en una fila de una tabla con los valores en el objeto comercial cuando se guarda el objeto, la función ACTUALIZAR () en un desencadenante se vuelve inútil. En ese caso, es probable que su código siempre actualice todas las columnas con cada instrucción UPDATE emitida desde el nivel medio. Siendo ese el caso, la función UPDATE (nombre de columna) siempre se evaluaría como verdadera cuando se guarden sus objetos comerciales porque todos los nombres de columna siempre se incluyen en las instrucciones de actualización. En ese caso, no sería útil utilizar UPDATE () en el desencadenador y solo sería extra adicional en ese desencadenante la mayoría de las veces.

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 sugirió 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

La sugerencia de GBN de usar una cláusula IF (EXISTS (...) y poner la lógica en esa declaración IF si existen filas modificadas podría funcionar. Ese enfoque disparará para TODAS las filas incluidas en el desencadenador, incluso si solo algunas de las filas fueron en realidad cambiado (que puede ser apropiado para su solución, pero también puede no ser apropiado si solo desea hacer algo en las filas donde cambiaron los valores). Si necesita hacer algo en las filas donde ha ocurrido un cambio real, necesita una lógica diferente en su SQL que proporcionó.

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

Answer 5

Creo que el siguiente código es mejor que los ejemplos anteriores porque se centra únicamente en las columnas que desea verificar de 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 las filas de la consulta izquierda que no se encuentran también en la consulta correcta. Este código se puede usar en INSERTAR y ACTUALIZAR desencadenantes.

La columna "PrimaryKeyID" es la clave principal de la tabla (puede ser varias columnas) y es necesaria para habilitar 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 modificadas en la lógica de desencadenante posterior, normalmente 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 :-)