Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Creating Triggers for INSERT Statements Only

By: FYIcenter.com

(Continued from previous topic...)

How To Create a Trigger for INSERT Only?

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.

(Continued on next topic...)

  1. What Are Triggers?
  2. What Are the Basic Features of a Trigger?
  3. How To Create a Simple Table to Test Triggers?
  4. How To Create a DML Trigger using CREATE TRIGGER Statements?
  5. How To Test a DML Trigger?
  6. How To List All Triggers in the Database with sys.triggers?
  7. How To Modify Existing Triggers using "ALTER TRIGGER"?
  8. How To Delete Existing Triggers using "DROP TRIGGER"?
  9. How To Get the Definition of a Trigger Back?
  10. How To Disable Triggers using "DISABLE TRIGGER"?
  11. How To Create a Trigger for INSERT Only?
  12. How To See the Event List of an Existing Trigger using sys.trigger_events?
  13. How To Access the Inserted Record of an Event?
  14. How To Access the Deleted Record of an Event?
  15. How To Improve the Trigger to Handle NULL Values?
  16. What Happens to a Trigger with Multiple Affected Rows?
  17. How To Override DML Statements with Triggers?
  18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
  19. Can You Roll Back the DDL Statement in a Trigger?
  20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...