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, 1734🔥, 0💬
Popular Posts:
How REAL and FLOAT Literal Values Are Rounded in SQL Server Transact-SQL? By definition, FLOAT(n) sh...
How To Select All Columns of All Rows from a Table with a SELECT statement in SQL Server? The simple...
What Are the Differences between DATE and TIMESTAMP in Oracle? The main differences between DATE and...
How To Update Multiple Rows with One UPDATE Statement in SQL Server? If the WHERE clause in an UPDAT...
How To Insert New Line Characters into Strings in SQL Server Transact-SQL? If you want to break a st...