MS SQL Server Interview Question:

What Are the Basic Features of a Trigger in MS SQL Server?

Submitted by: Administrator

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.
Submitted by: Administrator