SQL Server FAQ - Basic Features of a Trigger

Home >> FAQs/Tutorials >> SQL Server FAQ

By: FYIcenter.com

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