Tuesday, 20 August 2013

Trigger Status

Have you ever spent hours looking at an issue only to have your investigation hindered by a trigger?  I know I have and on more than one occasion!  This little script can be added to a SQL Agent Job and scheduled as you require to email an operator with a list of all triggers for all user databases, the table they are on and the status.


/*
      -----------------------------------------------------------------
      Trigger Status
      -----------------------------------------------------------------
    
      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------

*/
-- Set database context
USE master
GO

-- Declare variables
DECLARE @EmailProfile VARCHAR(255)
DECLARE @EmailRecipient VARCHAR(255)
DECLARE @EmailSubject VARCHAR(255)

-- Set variables
SET @EmailProfile = 'SQLReports';
SET @EmailRecipient = 'Chris@SQLServer365.co.uk';

--Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#TriggerStatus') IS NOT NULL
    DROP TABLE #TriggerStatus ;
   
-- Create temporary table   
CREATE TABLE #TriggerStatus
(
      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'

-- Check for unused indexes
IF EXISTS ( SELECT  1
            FROM #TriggerStatus)
    BEGIN
        DECLARE @tableHTML NVARCHAR(MAX); 
        SET @tableHTML = N'<style type="text/css">'
            + N'.h1 {font-family: Arial, verdana;font-size:16px;border:0px;background-color:white;} '
            + N'.h2 {font-family: Arial, verdana;font-size:12px;border:0px;background-color:white;} '
            + N'body {font-family: Arial, verdana;} '
            + N'table{font-size:12px; border-collapse:collapse;border:1px solid black; padding:3px;} '
            + N'td{background-color:#F1F1F1; border:1px solid black; padding:3px;} '
            + N'th{background-color:#99CCFF; border:1px solid black; padding:3px;}'
            + N'</style>' + N'<table border="1">' + N'<tr>'
            + N'<th>DatabaseName</th>'
            + N'<th>TableName</th>'
            + N'<th>TriggerName</th>'
            + N'<th>TriggerStatus</th>'
            + N'</tr>'
            + CAST(( SELECT td = DatabaseName,
                            '',
                            td = TableName,
                            '',
                            td = TriggerName,
                            '',
                            td = TriggerStatus,
                            ''                       
                     FROM   #TriggerStatus
                   FOR
                     XML PATH('tr') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N'</table>'; 
     
            -- Set subject
            SET @EmailSubject = 'Trigger Status Report For ' + @@SERVERNAME
           
            -- Email results 
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body = @tableHTML, @body_format = 'HTML'; 
    END

    GO

Remember, fully understanding your environment, the features you use and what is the norm is something that pays dividends when things go bad.

Enjoy!

Chris