Triggers
(Page 1 of 4 )
Triggers are procedures that are similar to events, but they have some unique qualities. This article will show you the different types of triggers, explain how to manage them, and then make trigger design recommendations. The first of several parts, it is excerpted from chapter nine of the book
Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281).
Triggers are a unique type of procedure. They are very similar to events—a type of procedure in certain programming languages such as Visual Basic. Events in Visual Basic are initiated by the system when certain actions occur (for instance, a form is loaded, a text box receives focus, or a key is pressed). Triggers are associated with SQL Server objects (such as tables) and executed by SQL Server when a specific change occurs.
In the following sections, we’ll first examine conceptually different types of triggers: the classic (After) triggers, the Instead-of triggers, and finally DDL triggers. In the remaining sections, we’ll discuss methods for managing triggers from Transact-SQL code and in Management Studio, and then finally discuss trigger design recommendations.
DML Triggers
SQL Server 7.0 and earlier versions recognized only one type of trigger. In SQL Server 2000 and SQL Server 2005, this type is called an After trigger. SQL Server 2000 introduced a new type—the Instead-of trigger. Both types are fired on the same types of modification statements:
Since these statements are considered Data Modification Language (DML), After triggers and Instead-of triggers are jointly called DML triggers.
Physical Design of After Triggers
The following is the simplified syntax for implementing the core functionality of After triggers:
Create Trigger trigger_name
On table
{After { [Delete] [,] [Insert] [,] [Update] }
As
sql_statement [...n]
As a stored procedure, a trigger logically consists of
- A header, which is a Transact-SQL statement for creating a trigger. It consists of three components:
- The name of the trigger
- The name of the table with which the trigger will be associated
- A modification statement (that is, an event) that will initiate the trigger
- A body, which contains Transact-SQL statement(s) to be executed at runtime.
The following example first creates a new table called MyEquipment, then populates it with Make and Model information from the Equipment table, and finally creates a trigger. The trigger is named trMyEquipment_D and is associated with the MyEquipment table. It is fired after a Delete statement is executed against the table. Its function is very simple—it notifies the user regarding actions and the number of records that have been deleted.
Create Table dbo.MyEquipment
(Id int identity,
Description varchar(500))
GO
-- populate table
Insert dbo.MyEquipment(Description)
Select top 5 Make + ' ' + Model from dbo.Equipment
GO
Create Trigger dbo.trMyEquipment_D
On dbo.MyEquipment
After Delete -- For Delete
As
Print 'You have just deleted '
+ Cast(@@rowcount as varchar)
+ ' record(s)!'
Go
To execute the trigger, you need to execute the Delete statement:
Delete dbo.MyEquipment
Where Id = 2
SQL Server returns the following:
You have just deleted 1 record(s)!
(1 row(s) affected)
You can also execute the Delete statement to delete multiple records:
Delete dbo.MyEquipment
Even in this case, the trigger will not be fired once for each record. You will receive just one message:
You have just deleted 4 record(s)!
(4 row(s) affected)
For this reason, it is important to design your trigger to handle actions against multiple records. You will see more reasons in following paragraphs.
Next: Inserted and Deleted Virtual Tables >>
More MS SQL Server Articles
More By McGraw-Hill/Osborne
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 8 
