How To Create a Trigger for INSERT Only in SQL Server?

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.

⇒Creating and Managing Triggers in SQL Server

⇒⇒SQL Server Database Tutorials