Managing Triggers

You can manage triggers using GUI tools such as SQL Server Management Studio or Visual Studio, or by using Transact-SQL statements.

Managing DML Triggers in Management Studio

You can access triggers from Management Studio:

1. Expand the node of the table with which the trigger is associated.

2. Expand the trigger's node.

3. Right-click the trigger and choose Modify from the pop-up menu.

SQL Server displays a form for editing trigger properties (see Figure 9-1). This editor is very similar to the editor you use to edit stored procedures.

Figure 9-1: Managing triggers in Management Studio

To create a new trigger on the table, right-click the trigger's subnode of the table and choose New Trigger from the pop-up menu. SQL Server initially fills the form with a template for creating a new trigger.

Once you have created or modified the trigger, you can choose Assisted Editor | Check Syntax to verify it, and then File | Save to attach it to the table.

You can delete a trigger by right-clicking the trigger and choosing Delete from the pop-up menu.

Managing DDL Triggers from Management Studio

Database-level DDL triggers are managed from server | Databases | database | Programmability | Database Triggers (see Figure 9-2). Server-level DDL triggers are managed from server | Programmability | Server Triggers.

Managing Triggers Using Transact-SQL Statements

SQL Server has a rich palette of system stored procedures, functions, and views for managing triggers from Transact-SQL.

Figure 9-2: Managing database-level DDL triggers

Listing Triggers

To list triggers associated with a table, use the system stored procedure sp_helptrigger:

 Exec sp_helptrigger 'dbo.OrderHeader' 

The server returns the list of triggers associated with the specified table and displays the type of trigger found in the isupdate, isdelete, isinsert, isafter, and isinteadof columns (see Figure 9-3).

Figure 9-3: Trigger information from sp_helptrigger

Viewing Triggers

You can obtain the code for a trigger using the system stored procedure sp_helptext:

 Exec sp_helptext 'dbo.trOrderStatus_U' 

The server returns the code for the specified trigger:

 Text ------------------------------------------------------------- CREATE Trigger trOrderStatus_U On dbo.[OrderHeader] After Update     -- For Update As      If Update (OrderStatusId)      Begin          Insert into ActivityLog( Activity,                                   LogDate,                                   UserName,                                   Note)     Select       'Order-Header.OrderStatusId' ,                  GetDate() ,                  USER_NAME(),                  'Value changed from '                  + Cast( d.OrderStatusId as varchar)                  + ' to '                  + Cast( i.OrderStatusId as varchar)     From deleted d inner join inserted i     On d.OrderId = i.OrderId End 

Deleting DML Triggers

A trigger can be deleted, as can all other database objects, using the appropriate Drop statement:

 if  exists (select * from sys.objects             where  object_id  =   object_id(N'[dbo].[trEquipment_IU]')             and  type   in   (N'TA',   N'TR')) DROP   TRIGGER    [dbo].[trEquipment_IU] 

Deleting DDL Triggers

The statement for dropping DDL triggers contains a reference to the scope (level) of the trigger:

 if  exists (select * from master.sys.server_triggers      where name = N'trdTestCreateLogin2' and parent_class=100) DROP TRIGGER [trdTestCreateLogin2] ON ALL SERVER GO if  exists (select * from sys.triggers      where name = N'trdAuditTableChanges' and parent_class=0) DROP TRIGGER [trdAuditTableChanges] ON DATABASE 

Note 

You should check the presence of server-level triggers in the master.sys.server_riggers view and the presence of database-level triggers in the sys.triggers view.

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_Triggerl', '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