Trigger compare inserted to deleted data issue

Question

I have faced below issue last time and just wondering why

null <> 'value'

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

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

`NULL` is a somewhat non-standard value. You cannot check for it using `=`, but could use `WHERE value IS NULL`, or in your case, `WHERE I.NAME IS NOT NULL`. — SchmitzIT, Jun 14 '17 at 09:44
I know this but in my case I want to insert data whenever data in main table has been changed so where value is not null cannot be applicable — Pawel Czapski, Jun 14 '17 at 09:47
Thank you very much for you input lads, I am going to upvote your answers. I decide later which answer should be accepted. — Pawel Czapski, Jun 14 '17 at 10:30

GuidoG · Accepted Answer · 2017-06-14T09:54:27.593

2

You cannot use normal comparision with null

these wont work:

if null <> value
if null = value

testing on null has to be like this :

if value is not null
if value is null

Or like this

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

in your case replace

WHERE D.NAME <> I.NAME

with this

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

That should fix your problem

Also change this

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

to this

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

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

edited Jun 14 '17 at 09:54

answered Jun 14 '17 at 09:49

GuidoG

11,359
6
44
79

WHERE I.NAME IS NOT NULL will not resolve the problem, wil not work if I will update from not null to null value. I have solution to the problem, but just wondering why my forst trigger didn't work even if I.NAME value = null – Pawel Czapski Jun 14 '17 at 09:53
I.NAME can never = null it can only be I.NAME is null – GuidoG Jun 14 '17 at 09:56
concatination is not an issue, it is only sample data, WHERE isnull(D.NAME, '') <> isnull(I.NAME, '') is already in my answer as solution for the problem. My question is why trigger behave like that? – Pawel Czapski Jun 14 '17 at 09:56
because that is how sql works. you cannot compare on null values. Every database is like that. It is normal behaviour – GuidoG Jun 14 '17 at 09:57
yeap, I know this. Looks like I cannot avoid expensive ISNULL function. – Pawel Czapski Jun 14 '17 at 10:05
I am afraid not, we all have to do this. Btw isnull is not expensive so no problem there – GuidoG Jun 14 '17 at 10:26

SchmitzIT · Answer 2 · 2017-06-14T09:52:26.187

1

The issue is that you cannot check for a NULL value using =. NULL is special, but can still be checked using 'IS NULL' or 'IS NOT NULL'

Try the following code:

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

Update: I just saw your reply to my comment. Would this do the trick?

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

edited Jun 14 '17 at 09:52

answered Jun 14 '17 at 09:47

SchmitzIT

9,227
9
65
92

As I said I want to insert records if any change occur, this will not work if I change data from not null value to null value... – Pawel Czapski Jun 14 '17 at 09:49
didn't work either, please read my question carefully. I have solution to the proble, my question is why it is behave like that, why first verion of the trigger doesn't work? – Pawel Czapski Jun 14 '17 at 09:58
@PawelCz It's essentially still that you cannot compare something to `NULL`. You can't even compare `NULL` to `NULL` (Try: `SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END` will yield 0). If you want the code to also do a `NULL` comparison, you will explicitly need to define it as such (as in my update). This is a limitation of SQL as a whole (not specific to SQL Server). Also see: https://stackoverflow.com/questions/15929269/issues-with-sql-comparison-and-null-values – SchmitzIT Jun 14 '17 at 11:17
Thanks, I am aware of this. ISNULL is not only way but the simpliest one. – Pawel Czapski Jun 14 '17 at 11:46
@PawelCz No worries. But yeah, NULLS are/can be a real pain. I've often banged my head against the desk because of NULLs :D – SchmitzIT Jun 14 '17 at 11:49

Zohar Peled · Answer 3 · 2017-06-14T10:27:25.827

1

That's because NULL is defined as an unknown value, meaning it can't be compared to.
All of the following statements will result in an empty recordset:

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

While these statements will return 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

By the way, and you are concatenating a string with NULL, you'll get NULL in return,
so SELECT D.NAME + ',' + I.NAME will return null if the name was null before the update or is being updated to null.
To avoid that you can use this technique:

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

That will return NULL only if both values are null, but if any of them is not null it will return only it. The STUFF is used to remove the first comma in case one of the values is not null.

If you are looking for a way to avoid the use of ISNULL, you can simply do this:

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
)

This way, if any one of them is null your where clause will return true, as well as if none of them is null but they hold different values.

Of course, using ISNULL will provide a shorter, easier to maintain code, but you are the one that wrote you wish to avoid it...

edited Jun 14 '17 at 10:27

answered Jun 14 '17 at 09:49

Zohar Peled

79,642
10
69
121

thnx, but concatination is not an issue here. I know null + 'value' will give me null. I had problem with WHERE D.NAME <> I.NAME which I had to replace by expensive WHERE ISNULL(D.NAME,'') <> ISNULL(I.NAME,''). Was only wondering why first version doesn't work. – Pawel Czapski Jun 14 '17 at 10:10
Well, the first part of my answer tells you the reason exactly. You can't compare an unknown value to anything, not even to another unknown value. That's why any attempt to use `=`, `<`, `>` or `<>` with null will always return false. – Zohar Peled Jun 14 '17 at 10:13
Please read the last part of my answer, it refers to a comment you gave to another answer. – Zohar Peled Jun 14 '17 at 10:16
Thnx for your input, that will create record if update null value to null value what is not desire. Solution for this is to use ISNULL function as I pointed in my question. – Pawel Czapski Jun 14 '17 at 10:24
Then just add another condition... see my edited answer. – Zohar Peled Jun 14 '17 at 10:27

score 1 · Answer 4 · answered Jun 14 '17 at 10:12

The behaviour off null comparison depends on ANSI_NULLS option. Try this code:

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'

So you see, with ansi_nulls off the behaviour is as you expected but it's the legacy option and

In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error.

So I think for one reason or another, you get to use this option set to off, but your trigger was created with ansi_nulls on, so when the rest of your code can work fine, when the trigger fires it uses ansi_nulls saved with it when it was created

Trigger compare inserted to deleted data issue

4 Answers4

Linked