Collections:
"INSTEAD OF" - Overriding DML Statements with Triggers in SQL Server
How To Override DML Statements with Triggers in SQL Server?
✍: FYIcenter.com
Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to cancel the UPDATE statement.
There is no easy way to cancel the DML statement in a DML trigger. But there is easy way to override the DML statement with an "INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on when the defined trigger will be fired:
The tutorial exercise below shows you how define an "INSTEAD OF" trigger on fyi_users to validate email addresses:
USE FyiCenterData; GO CREATE TRIGGER check_email ON fyi_users INSTEAD OF UPDATE AS DECLARE @count INT; SELECT @count = COUNT(*) FROM INSERTED WHERE email NOT LIKE '%_@_%'; IF @count = 0 UPDATE fyi_users SET email=i.email FROM INSERTED AS i WHERE fyi_users.id = i.id ELSE PRINT 'Invalid email(s) found.'; GO -- invalid email UPDATE fyi_users SET email='john.king' WHERE name = 'John King'; GO Invalid email(s) found. -- valid email UPDATE fyi_users SET email='john@fyicenter' WHERE name = 'John King'; GO Email changed from gniK nhoJ to john@fyicenter -- change all UPDATE fyi_users SET email='dba@fyicenter'; GO Email changed from ramuK craM to dba@fyicenter Email changed from hsuB yoR to dba@fyicenter Email changed from etaG kcaJ to dba@fyicenter Email changed from grebneerG ycnaN to dba@fyicenter Email changed from john@fyicenter to dba@fyicenter
The "invalid email" test shows that trigger check_email did stoped the UPDATE statement. John King's email did not get updated.
The "valid email" test shows that trigger check_email properly updated the email column, if the new email is valid. The reported message was generated from trigger update_user.
⇒ "CREATE TRIGGER" - Creating a DDL Trigger in SQL Server
⇐ Triggers with Multiple Affected Rows in SQL Server
2016-10-22, 1469🔥, 0💬
Popular Posts:
What Are Date and Time Functions in MySQL? MySQL offers a number of functions for date and time valu...
What Happens If the UPDATE Subquery Returns Multiple Rows in MySQL? If a subquery is used in a UPDAT...
How To Install PHP on Windows in MySQL? The best way to download and install PHP on Windows systems ...
How To Calculate Age in Days, Hours and Minutes in SQL Server Transact-SQL? On many Web sites, news ...
Where to find SQL Server Transact-SQL language references? You can find SQL Server Transact-SQL lang...