How to know when a trigger was disabled or disabled??

[prince]

Is there any way from which we can identify when a trigger was enabled or disabled in sql server??

 

[redlam]

As far as I know, triggers are 'enabled' the moment they are created. There is a 'crdate' field in the system table 'sysobjects'. My suggestion is that you run the following sql statement to determine when the trigger was created.

select name, crdate
from sysobjects
where type = 'tr'

 

[clawton]

You can tell whether a trigger is enabled or disabled at any point in time by ANDing the status field in the sysobjects with 2048. If True, the trigger is disabled otherwise it's enabled :

SELECT Name,
(status & 2048)
FROM sysobjects
WHERE type = 'TR'

I don't know a way of telling historically WHEN the change was made....I guess you could put a trigger on the SYSOBJECTS table itself that writes an entry to an audit table when the STATUS column changes.

Just an idea though.



Chris Lawton
Chris.Lawton@GoldMine.com