Designing and Modifying Triggers
(Page 1 of 4 )
Last week, we discussed trigger management. This week, we will learn how to modify triggers, and look at some recommendations for how to use and design triggers. The fourth of five parts, 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).
Modifying Triggers
Earlier in this chapter, you saw details of the syntax of a Transact-SQL statement for creating triggers. Triggers can be modified using the Alter Trigger statement. Since the features of the Alter Trigger and Create Trigger statements are identical, we will not explore the syntax a second time.
It is much better to use the Alter Trigger statement to modify a trigger than to drop and then re-create the trigger. During the period between dropping and creating a trigger, a user might make a change to the table, the consequence of which is that the rules that are usually enforced by the trigger will not be enforced.
NOTE
Keep in mind that the order of execution is lost when the trigger is altered—you must reset it using sp_SetTriggerOrder.
Renaming Triggers
Triggers are often renamed using Transact-SQL statements designed for the creation and modification of triggers, such as Alter Trigger. As with all other database objects, a trigger can be forced to change its name using the following system stored procedure:
Exec sp_rename 'Orders_Trigger1', 'trOrders_IU'
The first parameter is the current name of the database object, and the second parameter is the new name of the object.
Disabling DML Triggers
It is possible to temporarily disable and enable triggers without dropping them:
Alter Table [OrderHeader] Disable Trigger trOrders_IU
After the execution of this statement, the specified trigger will not fire, but it will still be associated with the table. This technique is often used to load large amounts of data into a table without initiating the business logic encapsulated in a trigger.
An alternative statement has been added to SQL Server 2005 (to make it compatible with statements for DDL triggers):
DISABLE TRIGGER dbo. trOrderHeader_IU ON dbo.OrderHeader
Disabling DDL Triggers
Disable Trigger statements for DDL triggers contain a reference to trigger scope (Database or All Server):
DISABLE TRIGGER trdAuditLoginCreation ON All Server
Next: Trigger Design Recommendations >>
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.
|
|
/ 5 
