I was reading through a problem where it was required that one should be able to find out whether some triggers are enabled or disabled. The suggested solution was to do a bitwise and of status field of sysobjects (with 2048 - 12th bit should be on) for record type = 'TR'. Now that will require a good understanding of system object internals. I was browsing through T-SQL help and found that objectproperty function already provides for a situation like this
Select
Left(o2.name,24) As TblName,
Left(o1.name,36) As TrgName,
Case (objectproperty(o1.id, 'ExecIsTriggerDisabled'))
When 0
Then 'Enabled'
Else 'Disabled'
End As TrgStatus
From sysobjects o1
Join sysobjects o2
On o1.Parent_Obj=o2.id
where o1.type='tr'
The objectproperty function allows to check for a range of properties without having to know the internals of system objects
e.g. (a few related to triggers)
ExecIsDeleteTrigger : bitwise code 256
ExecIsUpdateTrigger : bitwise code 512
ExecIsInsertTrigger : bitwise code 1024
Thought, I should share this with you all as you also may have similar requirements from the system tables.
RT
Select
Left(o2.name,24) As TblName,
Left(o1.name,36) As TrgName,
Case (objectproperty(o1.id, 'ExecIsTriggerDisabled'))
When 0
Then 'Enabled'
Else 'Disabled'
End As TrgStatus
From sysobjects o1
Join sysobjects o2
On o1.Parent_Obj=o2.id
where o1.type='tr'
The objectproperty function allows to check for a range of properties without having to know the internals of system objects
e.g. (a few related to triggers)
ExecIsDeleteTrigger : bitwise code 256
ExecIsUpdateTrigger : bitwise code 512
ExecIsInsertTrigger : bitwise code 1024
Thought, I should share this with you all as you also may have similar requirements from the system tables.
RT