MsSQL UPDATE event triggers are not fired when changing column value to null and from null to any value

[Hsoratsa]

Version Information

Server Version:
v2.45.2

Environment

OSS

What is the current behaviour?

When changing value in any nullable column(from null to value, or from value to null), the trigger is not fired

What is the expected behaviour?

When changing any nullable column(from null to value, or from value to null), the trigger must fired

How to reproduce the issue?

1. Create any table with any nulalble column
2. Create trigger on update
3. Change column value (from null to value, or from value to null)
4. See if trigger is fired

Screenshots or Screencast

Any possible solutions/workarounds you're aware of?

In trigger i see a comparison of !=, it should not be. This is the description of a possible solution:
The issue you're encountering is likely due to the way SQL Server handles NULL values in comparisons. When comparing NULL values using the != or <> operators, the result is UNKNOWN, which is treated as FALSE in the context of a WHERE clause. This means that if any of the columns being compared have NULL values, those rows will be excluded from the result set.

To handle NULL values correctly, you should use the IS NULL or IS NOT NULL conditions, or use the ISNULL or COALESCE functions to provide a default value for NULL comparisons.

For example this is the trigger created by hasura:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[notify_hasura_crud_One_UPDATE]
ON [dbo].[One]
AFTER UPDATE

AS
BEGIN
DECLARE @json_pk_not_updated NVARCHAR(MAX)
DECLARE @json_pk_updated NVARCHAR(MAX)

-- When primary key is not updated during a UPDATE transaction then construct both
-- 'data.old' and 'data.new'.
SET @json_pk_not_updated =
(SELECT
[DELETED].[CreateUser] as [payload.data.old.CreateUser], [INSERTED].[CreateUser] as [payload.data.new.CreateUser],
'UPDATE' as [payload.op],
'dbo' as [schema_name],
'One' as [table_name],
'crud_One' as [trigger_name]
FROM DELETED
JOIN INSERTED
ON [INSERTED].[ID] = [DELETED].[ID]
where [INSERTED].[CreateUser] != [DELETED].[CreateUser]
FOR JSON PATH
)

insert into hdb_catalog.event_log (schema_name,table_name,trigger_name,payload)
select * from OPENJSON (@json_pk_not_updated)
WITH(
schema_name NVARCHAR(MAX) '$.schema_name',
table_name NVARCHAR(MAX) '$.table_name',
trigger_name NVARCHAR(MAX) '$.trigger_name',
[payload] NVARCHAR(MAX) AS JSON
)

-- When primary key is updated during a UPDATE transaction then construct only 'data.new'
-- since according to the UPDATE Event trigger spec for MSSQL, the 'data.old' would be NULL
IF (1 = 1)
BEGIN
SET @json_pk_updated =
-- The following SQL statement checks, if there are any rows in INSERTED
-- table whose primary key does not match to any rows present in DELETED
-- table. When such an situation occurs during a UPDATE transaction, then
-- this means that the primary key of the row was updated.
(SELECT
NULL as [payload.data.old], [INSERTED].[CreateUser] as [payload.data.new.CreateUser],
'UPDATE' as [payload.op],
'dbo' as [schema_name],
'One' as [table_name],
'crud_One' as [trigger_name]
FROM INSERTED
WHERE NOT EXISTS (SELECT * FROM DELETED WHERE [INSERTED].[ID] = [DELETED].[ID] )
FOR JSON PATH, INCLUDE_NULL_VALUES
)

insert into hdb_catalog.event_log (schema_name,table_name,trigger_name,payload)
select * from OPENJSON (@json_pk_updated)
WITH(
  schema_name NVARCHAR(MAX) '$.schema_name',
  table_name NVARCHAR(MAX) '$.table_name',
  trigger_name NVARCHAR(MAX) '$.trigger_name',
  [payload] NVARCHAR(MAX) AS JSON
)

END

END;
GO
ALTER TABLE [dbo].[One] ENABLE TRIGGER [notify_hasura_crud_One_UPDATE]
GO

The correct and tested trigger is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[notify_hasura_crud_One_UPDATE]
ON [dbo].[One]
AFTER UPDATE

AS
BEGIN
DECLARE @json_pk_not_updated NVARCHAR(MAX)
DECLARE @json_pk_updated NVARCHAR(MAX)

-- When primary key is not updated during a UPDATE transaction then construct both
-- 'data.old' and 'data.new'.
SET @json_pk_not_updated =
(SELECT
DELETED.CreateUser as [payload.data.old.CreateUser], INSERTED.CreateUser as [payload.data.new.CreateUser],
'UPDATE' as [payload.op],
'dbo' as [schema_name],
'One' as [table_name],
'crud_One' as [trigger_name]
FROM DELETED
JOIN INSERTED
ON INSERTED.ID = DELETED.ID
WHERE (INSERTED.CreateUser != DELETED.CreateUser OR (INSERTED.CreateUser IS NULL AND DELETED.CreateUser IS NOT NULL) OR (INSERTED.CreateUser IS NOT NULL AND DELETED.CreateUser IS NULL))
FOR JSON PATH
)

insert into hdb_catalog.event_log (schema_name,table_name,trigger_name,payload)
select * from OPENJSON (@json_pk_not_updated)
WITH(
schema_name NVARCHAR(MAX) '$.schema_name',
table_name NVARCHAR(MAX) '$.table_name',
trigger_name NVARCHAR(MAX) '$.trigger_name',
[payload] NVARCHAR(MAX) AS JSON
)

-- When primary key is updated during a UPDATE transaction then construct only 'data.new'
-- since according to the UPDATE Event trigger spec for MSSQL, the 'data.old' would be NULL
IF (1 = 1)
BEGIN
SET @json_pk_updated =
-- The following SQL statement checks, if there are any rows in INSERTED
-- table whose primary key does not match to any rows present in DELETED
-- table. When such an situation occurs during a UPDATE transaction, then
-- this means that the primary key of the row was updated.
(SELECT
NULL as [payload.data.old], INSERTED.CreateUser as [payload.data.new.CreateUser],
'UPDATE' as [payload.op],
'dbo' as [schema_name],
'One' as [table_name],
'crud_One' as [trigger_name]
FROM INSERTED
WHERE NOT EXISTS (SELECT * FROM DELETED WHERE INSERTED.ID = DELETED.ID )
FOR JSON PATH, INCLUDE_NULL_VALUES
)

insert into hdb_catalog.event_log (schema_name,table_name,trigger_name,payload)
select * from OPENJSON (@json_pk_updated)
WITH(
  schema_name NVARCHAR(MAX) '$.schema_name',
  table_name NVARCHAR(MAX) '$.table_name',
  trigger_name NVARCHAR(MAX) '$.trigger_name',
  [payload] NVARCHAR(MAX) AS JSON
)

END

END;
GO
ALTER TABLE [dbo].[One] ENABLE TRIGGER [notify_hasura_crud_One_UPDATE]
GO

Keywords

mssql, event_trigger, null, nullable

[Hsoratsa]

in a short, the fix is to change this:
where [INSERTED].[CreateUser] != [DELETED].[CreateUser]
to this:
WHERE (INSERTED.CreateUser != DELETED.CreateUser OR (INSERTED.CreateUser IS NULL AND DELETED.CreateUser IS NOT NULL) OR (INSERTED.CreateUser IS NOT NULL AND DELETED.CreateUser IS NULL))

[seanparkross]

Thanks @Hsoratsa I'm checking with the team