I try to avoid using triggers because I always forget they are there. I have been burned more than once by triggers updating data when I didn’t know they were there. I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog. He wrote up a great article about trigger status. His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).
This script runs against master and will tell you about all triggers in all databases.
Use master
-- Create table variable
DECLARE @TriggerStatus as Table
(
DatabaseName SYSNAME,
TableName VARCHAR(255),
TriggerName VARCHAR(255),
TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?];
SELECT DB_NAME() AS DatabaseName,
OBJECT_NAME(parent_id) AS TableName,
name AS TriggerName,
CASE is_disabled
WHEN 0 THEN ''Enabled''
ELSE ''Disabled''
END AS TriggerStatus
FROM sys.triggers WITH ( NOLOCK )
WHERE is_ms_shipped = 0
AND parent_class = 1;
END'
SELECT DatabaseName, TableName, TriggerName, TriggerStatus FROM @TriggerStatus
posted @ Tuesday, August 20, 2013 5:17 PM