Basic Features of a Trigger in SQL Server

Q

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

✍: FYIcenter.com

A

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.

 

Creating a Simple Table to Test Triggers in SQL Server

What Are Triggers in SQL Server

Creating and Managing Triggers in SQL Server

⇑⇑ SQL Server Database Tutorials

2016-10-25, 1266🔥, 0💬