sql - update - Desencadenar problema de inserción a datos borrados

trigger sql server update

Question

Me he enfrentado a la cuestión de abajo la última vez y me pregunto por qué

nulo <> ''valor''

no funciona en el disparador Para resolver el problema, tuve que usar la función isnull

isnull (null, '''') <> isnull (''valor'', '''')

Todo el código para probar a continuación:

-- create main table
CREATE TABLE T_SAMPLE
(
    ID INT,
    NAME NVARCHAR(20)
)
GO

-- populate data in main table
INSERT INTO T_SAMPLE
VALUES (1, ''ONE''),
    (2,''TWO''),
    (3,''THREE'')
GO

-- create table to store changes
CREATE TABLE T_SMAPLE_TEST
(
    NAME NVARCHAR(40)
)
GO

-- create trigger on main table
CREATE TRIGGER [dbo].[TRG_SAMPLE]
   ON  [dbo].[T_SAMPLE]
   AFTER UPDATE
AS
BEGIN
    INSERT INTO T_SMAPLE_TEST
    SELECT D.NAME + '','' + I.NAME
    FROM INSERTED I
        INNER JOIN DELETED D
        ON I.ID = D.ID 
    WHERE D.NAME <> I.NAME
END
GO

-- ######### test ######### 
-- below works fine
UPDATE T_SAMPLE
SET NAME = ''ONE2''
WHERE ID = 1
GO

-- test data by running below selects
SELECT * FROM T_SMAPLE_TEST
SELECT * FROM T_SAMPLE

-- but when try to update to null value from not null or vice versa, it doesn''t work
UPDATE T_SAMPLE
SET NAME = NULL
WHERE ID = 1
GO

-- test data by running below selects
SELECT * FROM T_SMAPLE_TEST
SELECT * FROM T_SAMPLE

UPDATE T_SAMPLE
SET NAME = ''AGAIN''
WHERE ID = 1
GO

-- test data by running below selects
SELECT * FROM T_SMAPLE_TEST
SELECT * FROM T_SAMPLE

-- solution for this is to alter trigger as below
ALTER TRIGGER [dbo].[TRG_SAMPLE]
   ON  [dbo].[T_SAMPLE]
   AFTER UPDATE
AS
BEGIN
    INSERT INTO T_SMAPLE_TEST
    SELECT D.NAME + '','' + I.NAME
    FROM INSERTED I
        INNER JOIN DELETED D
        ON I.ID = D.ID 
    WHERE ISNULL(D.NAME,'''') <> ISNULL(I.NAME,'''')
END
GO

/*
DROP TABLE T_SMAPLE_TEST
DROP TABLE T_SAMPLE
DROP TRIGGER TRG_SAMPLE
*/

Answer 1

El comportamiento de comparación nula depende de la opción ANSI_NULLS . Prueba este código:

set ansi_nulls off
go
if ''jjj'' <> null print ''it works with ansi_nulls off''
go
set ansi_nulls on
go
if ''jjj'' <> null print ''it works ansi_nulls on''

Como puede ver, con ansi_nulls apagado el comportamiento es como esperaba, pero es la opción heredada y

En una versión futura de SQL Server, ANSI_NULLS siempre estará activado y cualquier aplicación que establezca explícitamente la opción en OFF generará un error.

Así que creo que por una razón u otra, puedes usar esta opción desactivada, pero tu activador se creó con ansi_nulls activado, por lo que cuando el resto de tu código puede funcionar bien, cuando el disparador usa ansi_nulls guardado con él cuando fue creado

Answer 2

El problema es que no puede verificar el valor NULL usando = . NULL es especial, pero aún se puede verificar con ''IS NULL'' o ''IS NOT NULL''

Pruebe el siguiente código:

ALTER TRIGGER [dbo].[TRG_SAMPLE]
   ON  [dbo].[T_SAMPLE]
   AFTER UPDATE
AS
BEGIN
    INSERT INTO T_SMAPLE_TEST
    SELECT D.NAME + '','' + I.NAME
    FROM INSERTED I
        INNER JOIN DELETED D
        ON I.ID = D.ID 
    WHERE I.NAME IS NOT NULL
END
GO

Actualización: acabo de ver su respuesta a mi comentario. ¿Sería esto el truco?

ALTER TRIGGER [dbo].[TRG_SAMPLE]
   ON  [dbo].[T_SAMPLE]
   AFTER UPDATE
AS
BEGIN
    INSERT INTO T_SMAPLE_TEST
    SELECT D.NAME + '','' + I.NAME
    FROM INSERTED I
        INNER JOIN DELETED D
        ON I.ID = D.ID 
    WHERE I.NAME <> D.NAME AND D.NAME IS NOT NULL
END
GO

Answer 3

Eso es porque NULL se define como un valor desconocido, lo que significa que no se puede comparar con.
Todas las siguientes declaraciones darán como resultado un conjunto de registros vacío:

SELECT 1 WHERE NULL = 1
SELECT 1 WHERE NULL <> 1
SELECT 1 WHERE NULL = NULL
SELECT 1 WHERE NULL <> NULL

Si bien estas declaraciones devolverán 1:

SELECT 1 WHERE ISNULL(NULL, 1) = 1
SELECT 1 WHERE ISNULL(NULL, 0) <> 1
SELECT 1 WHERE ISNULL(NULL, 0) = ISNULL(NULL, 0)
SELECT 1 WHERE NULL IS NULL

Por cierto, y estás concatenando una cadena con NULL, obtendrás NULL a cambio,
así que SELECT D.NAME + '','' + I.NAME devolverá nulo si el nombre fue nulo antes de la actualización o si se está actualizando a nulo.
Para evitar eso, puedes usar esta técnica:

SELECT  STUFF(
        ISNULL('',''+ D.NAME, '''') + 
        ISNULL('','' + I.NAME, '''')
        , 1, 1, '''')

Eso devolverá NULL solo si ambos valores son nulos, pero si alguno de ellos no es nulo, solo lo devolverá. El STUFF se usa para eliminar la primera coma en caso de que uno de los valores no sea nulo.

Si está buscando una manera de evitar el uso de ISNULL , simplemente puede hacer esto:

WHERE 
(
    I.NAME <> D.NAME
    OR I.NAME IS NULL
    OR D.NAME IS NULL
) 
AND NOT 
(
I.NAME IS NULL 
AND D.NAME IS NULL
)

De esta forma, si alguno de ellos es nulo, su cláusula where devolverá la verdad, así como si ninguno de ellos es nulo, pero tienen valores diferentes.

Por supuesto, el uso de ISNULL proporcionará un código más corto y fácil de mantener, pero usted es el que escribió que desea evitarlo ...

Answer 4

No puedes usar la comparación normal con null

estos no funcionarán:

if null <> value
if null = value

probar en null tiene que ser así:

if value is not null
if value is null

O así

if isnull(value, '''') = ''''
if isnull(value, '''') <> ''''

en su caso, reemplace

WHERE D.NAME <> I.NAME

con este

WHERE isnull(D.NAME, '''') <> isnull(I.NAME, '''')

Eso debería arreglar tu problema

También cambia esto

SELECT D.NAME + '','' + I.NAME

a esto

SELECT isnull(D.NAME, '''') + '','' + isnull(I.NAME, '''')

porque cuando uno de los nombres es nulo, toda la cadena concatenada también será nula