Technical Article

Query to Display Trigger Status

,

Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled.

SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled')

There are a number of other useful properties that can be returned, see help for the ObjectProperty command for more details.

The code below lists all tables that have a trigger and the status of that trigger.

SELECT t.[name] AS TableName,
tr.[name]AS TriggerName,
TriggerStatus = CASE
  WHEN OBJECTPROPERTY(tr.id, 'ExecIsTriggerDisabled') = 1 THEN 'Disabled'
  WHEN OBJECTPROPERTY(tr.id, 'ExecIsTriggerDisabled') = 0 THEN 'Enabled'
  ELSE 'ERROR'
END
FROMsysobjects t INNER JOIN sysobjects tr
  ON tr.parent_obj = t.id
WHEREt.xtype = 'U' AND
tr.xtype = 'TR' 
ORDER BY t.[name],
tr.[name]

Rate

5 (1)

Share

Share

Rate

5 (1)