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, 1383🔥, 0💬
Popular Posts:
How To Round a Numeric Value To a Specific Precision in SQL Server Transact-SQL? Sometimes you need ...
How To List All Stored Procedures in the Current Database in SQL Server Transact-SQL? If you want to...
Can You Drop an Index Associated with a Unique or Primary Key Constraint in Oracle? You can not dele...
Where to find answers to frequently asked questions on Storage Engines: MyISAM, InnoDB and BDB in My...
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL...