Collections:
Creating Triggers for INSERT Statements Only in SQL Server
How To Create a Trigger for INSERT Only in SQL Server?
✍: FYIcenter.com
The trigger, dml_message, provided in previous tutorials was defined to handle all 3 types of DML statements, INSERT, UPDATE, and DELETE.
If you do not want the trigger to handle all 3 types of DML statements, you can list only 1 or 2 of the statement keywords. For example, the following SQL script defines a trigger that only handle the INSERT statement events:
USE FyiCenterData GO CREATE TRIGGER new_user ON fyi_users AFTER INSERT AS PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE()) + ' New users added.'; GO INSERT INTO fyi_users (name) VALUES ('Marc Kumar'); GO Time: Jul 1 2007 Records are inserted, updated, or deleted in fyi_users Time: Jul 1 2007 New users added. (1 row(s) affected) UPDATE fyi_users SET email='marc@fyicenter' WHERE name = 'Marc Kumar'; GO Time: Jul 1 2007 Records are inserted, updated, or deleted in fyi_users (1 row(s) affected)
Notice that the INSERT statement triggered two triggers to be executed: dml_message and new_user. But the UPDATE statement triggered one trigger to be executed: dml_message as expected.
It is also interesting to know that when multiple triggers are defined to handle the same event, the oldest (defined first) will be executed first.
⇒ sys.trigger_events - Event List of an Existing Trigger in SQL Server
⇐ "DISABLE TRIGGER" - Disabling Triggers in SQL Server
2016-10-24, 1669🔥, 0💬
Popular Posts:
How To List All User Names in a Database in SQL Server? If you want to see a list of all user names ...
Where to find SQL Server database server tutorials? Here is a collection of tutorials, tips and FAQs...
How To Start Instance with a Minimal Initialization Parameter File in Oracle? The sample initializat...
How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL? Since Unicode ch...
How To Recover a Dropped Index in Oracle? If you have the recycle bin feature turned on, dropped ind...