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, 3796🔥, 0💬
Popular Posts:
How to download and install Microsoft SQL Server Management Studio Express in SQL Server? Microsoft ...
Where to find answers to frequently asked questions on Conditional Statements and Loops in SQL Serve...
How To Connect ASP Pages to Oracle Servers in Oracle? If you are running Windows IIS Web server and ...
How To Assign Debug Privileges to a User in Oracle? In order to run SQL Developer in debug mode, the...
How To Break Query Output into Pages in MySQL? If you have a query that returns hundreds of rows, an...