MS SQL Server Interview Question:

How To Create a Trigger for INSERT Only?

Submitted by: Administrator

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 GlobalGuideLineDatabase
GO

CREATE TRIGGER new_user ON ggl_users
AFTER INSERT
AS
PRINT 'Time: '+CONVERT(VARCHAR(12),GETDATE())
+ ' New users added.';
GO

INSERT INTO ggl_users (name) VALUES ('Marc MHI');
GO
Time: Jul 1 2007
Records are inserted, updated, or deleted in ggl_users
Time: Jul 1 2007 New users added.
(1 row(s) affected)
Submitted by: Administrator

UPDATE ggl_users SET email='marc@ggl'
WHERE name = 'Marc MHI';
GO
Time: Jul 1 2007
Records are inserted, updated, or deleted in ggl_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.
Submitted by: Administrator