Systems Engineering and RDBMS

Trigger Status

Posted by decipherinfosys on July 22, 2007

In SQL Server, in order to find out whether triggers are enabled or disabled, we can query the meta data tables. Using the ObjectProperty() function, we can write up a simple script to see whether the trigger in question is disabled or enabled.

SELECT
CASE OBJECTPROPERTY(OBJECT_ID(‘TEST_TRIGGER’), ‘ExecIsTriggerDisabled’)
WHEN 0 THEN ‘ENABLED’
ELSE ‘DISABLED’
END

The above statement can be run from Query Analyzer (SQL Server 2000) or SSMS (SQL Server 2005). OBJECT_ID and OBJECTPROPERTY are in-built functions. In the above statement, OBJECT_ID(‘TEST_TRIGGER’) will return the id of the ‘TEST_TRIGGER’ object. OBJECTPROPERTY requires two arguments (id, property). property is an expression containing the information to be returned for the object specified by id. For various properties refer to BOL. If a developer wants to see the status of all triggers in the database, then the following query will be useful.

SELECT
OBJECT_NAME(PARENT_OBJ) TABLE_NAME,
NAME AS TRIGGER_NAME,
CASE OBJECTPROPERTY(ID, ‘EXECISTRIGGERDISABLED’)
WHEN 0 THEN ‘ENABLED’
ELSE ‘DISABLED’
END AS STATUS
FROM SYSOBJECTS
WHERE XTYPE = ‘TR’

The above query will return TABLE_NAME and associated TRIGGER_NAME along with its STATUS. If using SQL Server 2005, you can use sys.sysobjects instead of sysobjects though the above query will work fine as well. It is usually advisable to use the Information_Schema views when dealing with meta-data but not all the information can be obtained from those and hence one needs to understand the system tables and views as well to deal with the meta-data.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: