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.
⇒ Triggers with Multiple Affected Rows in SQL Server
⇐ "DELETED" - Old Record of an DML Event Instance in SQL Server
2016-10-24, 1803🔥, 0💬
Popular Posts:
What are binary literals supported in SQL Server Transact-SQL? Binary literals in Transact-SQL are s...
What Is ISAM in MySQL? ISAM (Indexed Sequential Access Method) was developed by IBM to store and ret...
How to connect SQL Server Management Studio Express to SQL Server 2005 Express in SQL Server? Once y...
How To Enter Unicode Character String Literals in SQL Server Transact-SQL? Unicode characters are mu...
How to continue to the next iteration of a WHILE loop in SQL Server Transact-SQL? How to use CONTINU...