885
Chapter 36: Creating Triggers
36
■ (^) CREATE, ALTER, or DROP database
■ RECONFIGURE
■ (^) RESTORE database or log
Disabling Triggers
DML statements cannot bypass a trigger, but a system administrator, database owner, or
table owner can temporarily disable it.
Disabling a trigger occurs on the table, not just the current connection or the current user. This could have unin-
tended consequences and should not be undertaken lightly.
To temporarily turn off a trigger, use the ALTER TABLE DDL command with the
ENABLE TRIGGER or DISABLE TRIGGER option:
ALTER TABLE schema.TableName ENABLE or DISABLE TRIGGER
schema.TriggerName;
For example, the following code disables the INSTEAD OF trigger (TriggerOne on the
Person table):
ALTER TABLE HumanResources.Department
DISABLE TRIGGER TriggerOne;
To view the enabled status of a trigger, use the OBJECTPROPERTY() function, passing to it
the object ID of the trigger and the ExecIsTriggerDisabled option:
SELECT OBJECTPROPERTY(
OBJECT_ID(' HumanResources.TriggerOne'), 'ExecIsTriggerDisabled');
Listing Triggers
The following query lists all the triggers in the database based on the sys.triggers cata-
log view:
USE AdventureWorks2012
GO
SELECT Sc.name + '.'' + Ob.name as [table],
Tr.name as [trigger],
CASE (Tr.is_instead_of_trigger )
WHEN 0 THEN 'after'
WHEN 1 THEN 'instead of'
END AS type,
CASE (Tr.is_disabled)
WHEN 0 THEN 'enabled'
c36.indd 885c36.indd 885 7/31/2012 10:03:07 AM7/31/2012 10:03:07 AM
http://www.it-ebooks.info