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, 1891🔥, 0💬
Popular Posts:
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
Where to find answers to frequently asked questions on PHP Connections and Query Execution for MySQL...
How To Connect the Oracle Server as SYSDBA in Oracle? This is Step 4. The best way to connect to the...
How To Convert Numeric Values to Integers in SQL Server Transact-SQL? Sometimes you need to round a ...
How To Convert Binary Strings into Integers in SQL Server Transact-SQL? Binary strings and integers ...