SQL Server Performance Forum – Threads Archive

How to tell whether a trigger is enabled or not?

Hi, This question is for SQL Server 2000 and 2005.
Is there a function to tell whether a trigger is enabled or not? Which system table contains this info? Thanks, Peter

Trigger table is Sysobjects, but about enable or not I don’t know.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Try SELECT OBJECTPROPERTY(OBJECTID(‘<triggername>’), ‘ExecIsTriggerDisabled’). If return value = 0 then the trigger is enabled. I would not be surprised to learn that this information can be derived from the status column in one of the system tables, but as long as you have this function don’t bother with the underlying tables. The old warning about not querying system tables because Microsoft might make changes in future releases is becoming very true, as in SQL 2005 we can no longer query system tables (or so rumour has it).

Adriaan’s reference is still valid for SQL 2005 and as per his suggestion querying system tables is no longer supported in SQL 2005. http://msdn2.microsoft.com/en-us/library/ms176105.aspx for your information on OBJECTPROPERY statement. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>