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