Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Basic Features of a Trigger

By: FYIcenter.com

(Continued from previous topic...)

What Are the Basic Features of a Trigger?

Since a SQL Server trigger is a really an event handler, it has the following basic features similar to event handlers in other programming languages:

  • Event Type - It must be declared to handle a specific event, like a DELETE event.
  • Object Scope - It must be declared to handle events in a specific database object scope, like a specific table.
  • Statement Body - It must have a statement body, a batch of statements to be executed when the specified event occurs in specified database object scope. An event handler (trigger) with an empty statement body is useless.
  • Access of Event Attributes - It must have access to some attributes of the event, so it can have different logics for different instances of the event.

For example, you can implement a trigger to send a security alert message to each user whenever his or her password is changed. This trigger should have the following features:

  • Event Type - It must be declared to handle the UPDATE event.
  • Object Scope - It must be declared to handle the UPDATE event on the user password table only.
  • Statement Body - It must have a statement body to determine if the password is really changed or not. If it is changed, send an email to the user's email address.
  • Access of Event Attributes - It must have access to some attributes of the event instance, like the old value and the new value of the password, and the user email address.

(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...