Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - Basic Features of a Trigger
(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...)
- What Are Triggers?
- What Are the Basic Features of a Trigger?
- How To Create a Simple Table to Test Triggers?
- How To Create a DML Trigger using CREATE TRIGGER Statements?
- How To Test a DML Trigger?
- How To List All Triggers in the Database with sys.triggers?
- How To Modify Existing Triggers using "ALTER TRIGGER"?
- How To Delete Existing Triggers using "DROP TRIGGER"?
- How To Get the Definition of a Trigger Back?
- How To Disable Triggers using "DISABLE TRIGGER"?
- How To Create a Trigger for INSERT Only?
- How To See the Event List of an Existing Trigger using sys.trigger_events?
- How To Access the Inserted Record of an Event?
- How To Access the Deleted Record of an Event?
- How To Improve the Trigger to Handle NULL Values?
- What Happens to a Trigger with Multiple Affected Rows?
- How To Override DML Statements with Triggers?
- How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
- Can You Roll Back the DDL Statement in a Trigger?
- Can You Create a Logon Trigger in SQL Server 2005 Express Edition?