SQL Server FAQ - Improving the Trigger to Handle NULL Values

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

How To Improve the Trigger to Handle NULL Values?

When a NULL value is concatenated with a string, the result will be a null value. So if you want the trigger to properly report NULL values, you need to enhance the trigger as shown in the following tutorial example:

USE FyiCenterData;
GO

-- trigger executed but printed a NULL
UPDATE fyi_users SET email=NULL
   WHERE name = 'John King';
GO

(1 row(s) affected)

ALTER TRIGGER update_user ON fyi_users
AFTER UPDATE
AS
  DECLARE @new VARCHAR(80);
  DECLARE @old VARCHAR(80);
  SELECT @new = email FROM INSERTED;
  SELECT @old = email FROM DELETED;
  SELECT @new = ISNULL(email,'NULL') FROM INSERTED;
  SELECT @old = ISNULL(email,'NULL') FROM DELETED;
  PRINT 'Email changed from '+@old+' to '+@new;
GO

-- trigger is reporting NULL values now
UPDATE fyi_users SET email=NULL
   WHERE name = 'John King';
GO
Email changed from NULL to NULL
(1 row(s) affected)

The trigger is handling NULL values properly now.

(Continued on next topic...)