Managing Triggers | Microsoft SQL Server 2000 Administrators Companion

3 4

Now that you know how to create a trigger, you need to learn how to manage triggers. In this section, we'll look first at the T-SQL method of managing triggers and then at the Enterprise Manager method.

Managing Triggers by Using T-SQL

Several T-SQL commands are available to help you manage triggers. You can view trigger code, view the triggers that exist on a particular table, alter trigger code, drop triggers, and enable or disable triggers. All of these options are described in this section.

Viewing Trigger Code

Two system stored procedures provide information about triggers: sp_helptext and sp_helptrigger. Use the procedure sp_helptext followed by a trigger name to display the code used to create that trigger. For example, to view the definition of the Print_Update trigger created earlier, use the following command:

 USE MyDB GO sp_helptext Print_Update GO

The output looks like this:

 Text –––––––––––––––––––––––––––––––––––––––––––––––– CREATE TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE AS PRINT "The Bicycle Inventory table was updated."

Viewing the Triggers Existing on a Table

To view the triggers that exist on a particular table (or to see if no triggers exist), use the sp_helptrigger stored procedure followed by the table name. To view the triggers on the sample table MyTable, use the following command:

 USE MyDB GO sp_helptrigger MyTable GO

The output is shown here:

 trigger_name trigger_owner isupdate isdelete isinsert isafter isinsteadof –––––––––––– ––––––––––––– –––––––– –––––––– –––––––– ––––––– –––––––– Print_Update  dbo          1        0        0        1       0 (1 row affected)

The output shows the name of the trigger, the owner of the trigger, and the type of data modification event (or types of events) that will fire the trigger. The output columns isupdate, isdelete, isinsert, isafter, and isinsteadof contain the value 1 if the trigger fires for the type of data modification event indicated by the column name, or 0 if it does not. More than one column will contain a value of 1 if the trigger fires for more than one type of data modification event.

Using ALTER TRIGGER

To change the definition of a trigger, you can either drop and re-create the trigger or use the ALTER TRIGGER statement. This statement uses the same syntax as the CREATE TRIGGER statement. You must redefine the entire trigger if you modify it. For example, to alter our sample trigger Print_Update to fire upon the execution of either an INSERT statement or an UPDATE statement that affects Bicycle_Inventory, use the following code:

 USE MyDB GO ALTER TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE, INSERT AS PRINT "Bicycle_Inventory was updated or a row was inserted" GO

The old version of the trigger no longer exists; it has been replaced by the altered version. Now if you either update Bicycle_Inventory or insert data into it, the trigger will fire. Examples of the statements used to perform these operations are shown here:

 INSERT INTO Bicycle_Inventory VALUES ("Trek",1,"Lance S.E.",1,0,1) GO UPDATE Bicycle_Inventory SET    in_stock = 1 WHERE  model_name = "Lance S.E." GO

Using DROP TRIGGER

To delete a trigger from a table, use the DROP TRIGGER statement. You can drop a trigger that you no longer need. The syntax for this statement is shown here:

 DROP TRIGGER trigger_name

To drop our sample trigger Print_Update, use the following statement:

 USE Bicycle_Inventory GO DROP TRIGGER Print_Update GO

Now if you attempt to view the triggers that exist on the table MyTable by using the following T-SQL code, you will see that none exist:

 USE MyDB GO sp_helptrigger MyTable GO

NOTE
If you delete a table, all triggers on that table are automatically deleted as well.

Enabling and Disabling Triggers

You can use the ALTER TABLE statement to enable or disable a trigger without deleting the trigger definition from the table. Because each trigger is defined on a certain table, we use the ALTER TABLE statement instead of ALTER TRIGGER. Let's re-create the first sample trigger in this chapter to use as an example:

 USE MyDB GO IF EXISTS (SELECT name            FROM   sysobjects            WHERE name = "Print_Update" AND                  type = "TR") DROP TRIGGER Print_Update GO CREATE TRIGGER Print_Update ON Bicycle_Inventory FOR UPDATE AS PRINT "The Bicycle_Inventory table was updated" GO

At creation time, the trigger is automatically enabled. To disable the trigger so that it never fires (until re-enabled), but so its definition will still exist, as is, on the table, use the DISABLE TRIGGER option, as follows:

 ALTER TABLE Bicycle_Inventory DISABLE TRIGGER Print_Update GO

Now when an update occurs on Bicycle_Inventory, the Print_Update trigger does not fire. It will not fire again until it is explicitly re-enabled with the ENABLE TRIGGER option, as follows:

 ALTER TABLE Bicycle_Inventory ENABLE TRIGGER Print_Update GO

The ENABLE TRIGGER and DISABLE TRIGGER clauses are useful when you want to stop a trigger from firing but you want to retain the trigger in case you need to use it later. Your trigger definition remains intact, so you don't have to re-create the trigger later; you only have to re-enable it.

Managing Triggers by Using Enterprise Manager

With Enterprise Manager, you can manage your triggers through a graphical interface. However, you still need to know how to write the T-SQL code.

Deleting a Trigger

To delete a trigger by using Enterprise Manager, follow these steps:

Open the Trigger Properties window by right-clicking a table name, pointing to All Tasks in the shortcut menu that appears, and then choosing Manage Triggers from the All Tasks submenu.
In the Trigger Properties window, select the trigger name from the Name drop-down list, and click Delete.
A confirmation dialog box appears, as shown in Figure 22-5. Click Yes to delete the trigger you selected.

Figure 22-5. The dialog box in which you confirm a trigger deletion.

Modifying a Trigger

To modify a trigger by using Enterprise Manager, follow these steps:

Open the Trigger Properties window by right-clicking a table name, pointing to All Tasks in the shortcut menu that appears, and then choosing Manage Triggers from the All Tasks submenu.
Select the trigger name from the Name drop-down list.
Edit the T-SQL code in the Text box. Use Ctrl+Tab to indent the text. You can use the CREATE TRIGGER or ALTER TRIGGER statement. In either case, SQL Server will delete the existing trigger and re-create it for you. You can even use CREATE TRIGGER followed by the same trigger name as the existing name. You will not get an error in this case, as you would when using interactive OSQL or ISQL. When you have finished your edits, click Apply. SQL Server will then automatically modify the trigger definition.