触发比较插入到删除的数据问题 [英] Trigger compare inserted to deleted data issue

问题描述

我上次遇到以下问题，只是想知道为什么

I have faced below issue last time and just wondering why

null<>'值'

null <> 'value'

在触发器中不起作用。
要解决问题，我必须使用isull函数

doesn't work in trigger. To resolve issue I had to use isnull function

isnull（null，''）<> isull（'value' ，''）

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

下面要测试的所有代码：

All code to test below:

-- 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
*/

推荐答案

您不能使用常规比较

这些将不起作用：

if null <> value
if null = value

对null进行测试必须是这样的：

testing on null has to be like this :

if value is not null
if value is null

或类似

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

在您的情况下替换

WHERE D.NAME <> I.NAME

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

那应该解决您的问题

还要更改此

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

为此

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

因为当名称之一为空时，整个隐含字符串也将为空

because when one of the names is null the whole concatinated string will also be null

这篇关于触发比较插入到删除的数据问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！