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
FROM	sysobjects t INNER JOIN sysobjects tr
	  ON tr.parent_obj = t.id
WHERE	t.xtype = 'U' AND
	tr.xtype = 'TR' 
ORDER BY t.[name],
	tr.[name]

Rate

5 (1)

Share

Share

Rate

5 (1)