Managing Triggers

Triggers are a powerful and essential attribute of any DBMS, but they can be a headache to manage, especially when you have a lot of them. For this reason, if you have a big system, you will want to architect triggers in a modeling system and provide access to trigger metadata. The following sections explain how to alter and drop triggers.

Altering Triggers

To alter a trigger in T-SQL, you need to use the ALTER TRIGGER statement. The basic statement is as follows:

 ALTER TRIGGER trigger_name ON . . ..

Note 

Management Studio adds the alter trigger code automatically the first time you open the trigger code for editing.

The code to apply after the ON line takes the same syntax and choice of arguments as the CREATE TRIGGER statement described earlier (see the SQL Server 2005 Books Online for the full explanation and usage of the arguments).

Dropping Triggers

Dropping a trigger in T-SQL requires the DROP TRIGGER statement followed by the trigger name. Consider, for example, the following code:

 USE MYDB IF EXISTS (SELECT name FROM sysobjects    WHERE name = 'SecurityViolation' AND type = 'TR') DROP TRIGGER SecurityViolation

It drops the trigger SecurityViolation from MYDB.

You can specify multiple triggers in a DROP TRIGGER statement by separating trigger names with commas and enclosing the list in square brackets, as here:

 DROP TRIGGER [x, y, z].

You should make sure to check for trigger dependencies with the sp_depends stored procedure before dropping a trigger.

To drop a trigger interactively, simply drill down to the tables in your database and the Triggers folder. Then expand the list of triggers in the folder and right-click the trigger you wish to manage. From the context menu, you can select the trigger from the drop-down list and click Delete. You can also disable a trigger at this point.

Getting Information about Triggers

To obtain information from SQL Server about the triggers installed on a table, you should execute the system stored procedure sp_helptrigger as follows:

 --to check triggers installed on the table Items EXEC sp_helptrigger Items

The sp_helptrigger procedure returns information about the triggers on the table, the trigger owners, and the DML statements they are defined for. The full syntax of this stored procedure is as follows:

 sp_helptrigger [ @tabname = ] 'table' [ , [ @triggertype = ] 'type' ]

where the @triggertype option specifies the type of triggers you require information on. With the code

 sp_helptrigger @tabname = 'CRMCustomer'

the result set returned from the procedure is listed is as follows:

trigger_name	trigger_owner	Isupdate	isdelete	Isinsert	isafter	is instead of	Trigger_schema
CRMCustomerUpdateTrigger	dbo	1	0	0	1	0	dbo
CRMCustomerInsertCascadeTrigger	dbo	0	0	1	1	0	dbo