Collections:
Improving the Trigger to Handle NULL Values in SQL Server
How To Improve the Trigger to Handle NULL Values in SQL Server?
✍: FYIcenter.com
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.
2016-10-24, 680👍, 0💬
Popular Posts:
How To Change the Name of a Database User in SQL Server? If you want to change the name of an existi...
How to download and install the scaled-down database AdventureWorksLT in SQL Server? If you want to ...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...
How To Generate Random Numbers with the RAND() Function in SQL Server Transact-SQL? Random numbers a...