See the status of Triggers

GO

SET ANSI_NULLS ON

GO

ALTER  FUNCTION dbo.udf_Tbl_TriggerStatusTAB (

     @TABLENamePattern as sysname = NULL -- Tables to show,

                                         -- NULL for all

) RETURNS TABLE

  -- No schemabinding due to use of system tables.

/*

* Shows the enabled/disabled status of triggers that match

* the table name pattern.  Use NULL for triggers on all tables

* or a pattern for the LIKE operator to match names.

*

* Example:

select * from udf_Tbl_TriggerStatusTAB(null)

*

* History:

* When          Who     Description

* ------------- ------- ----------------------------------------

* 2003-11-10    ASN     Initial Coding

*

* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com

* You may use this function in any of your SQL Server databases

* including databases that you sell, so long as they contain

* other unrelated database objects. You may not publish this

* UDF either in print or electronically.

* Published in the T-SQL UDF of the Week Vol 2 #7 2/3/04

http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm

****************************************************************/

AS RETURN

    SELECT TOP 100 PERCENT WITH TIES

           T.[name] as TableName

         , TR.[Name] as TriggerName

         , CASE WHEN 1=OBJECTPROPERTY(TR.[id], 'ExecIsTriggerDisabled')

                THEN 'Disabled' ELSE 'Enabled' END Status

         FROM sysobjects T

             INNER JOIN sysobjects TR

                  on t.[ID] = TR.parent_obj

         WHERE (T.xtype = 'U' or T.XType = 'V')

            AND (@TableNamePattern IS NULL OR T.[name] LIKE @TableNamePattern)

            AND (TR.xtype = 'TR')

         ORDER BY T.[name]

                , TR.[name]

GO

GRANT SELECT on udf_TBL_TriggerStatusTAB to PUBLIC

GO

Start with some cleanup of previous tests:

-- Remove the existing testing table from a previous test
if OBJECT_ID('TriggerTester') is NOT NULL  drop table TriggerTester
SET NOCOUNT ON
PRINT 'Trigger Testing Script'
GO

(Results)
Trigger Testing Script

-- Create the TriggerTester Table
CREATE TABLE TriggerTester (
          [ID]        int IDENTITY (1,1)  PRIMARY KEY
        , RowDesc     VARCHAR(32) NOT NULL
        , LastUpdated datetime NULL
             )
PRINT 'Table created'
GO

(Results)
Table created

-- Create the Update trigger 
CREATE TRIGGER TriggerTester_Update_demo on TriggerTester 
          FOR UPDATE
AS

    UPDATE TriggerTester
         SET LastUpdated = GETDATE()
       FROM TriggerTester 
           inner join inserted
               on inserted.[ID] = TriggerTester.[ID]
GO

(Results)
The command(s) completed successfully.

-- Insert on row into the table and verify that LastUpdate is NULL
PRINT 'Inserting one row into TriggerTester'
INSERT INTO TriggerTester (RowDesc) VALUES ('Test Row')
SELECT *, getdate() NOW FROM TriggerTester
GO
(Results)
Inserting one row into TriggerTester
ID  RowDesc    LastUpdated          NOW                        
--- ---------- -------------------- -------------------------- 
1   Test Row   NULL                 2004-01-30 02:26:50.200   

-- Update and verify that the trigger has run, because LastUpdate shoould
-- be non-Null and the same as NOW
UPDATE TriggerTester  SET RowDesc = 'Test Row Updated'
SELECT *, getdate() NOW FROM TriggerTester
GO

(Results)
ID  RowDesc           LastUpdated              NOW     
--- ----------------- ------------------------ -----------------------
1   Test Row Updated  2004-01-30 02:29:17.403  2004-01-30 02:29:17.403

-- Show the staus of all triggers on the TriggerTester table
SELECT * FROM udf_Tbl_TriggerStatusTAB ('TriggerTester')
GO

(Results)
TableName           TriggerName                    Status   
------------------- ------------------------------ -------- 
TriggerTester       TriggerTester_Update_demo      Enabled

-- Disable the Trigger
ALTER TABLE TriggerTester DISABLE TRIGGER TriggerTester_Update_demo
SELECT *  FROM udf_Tbl_TriggerStatusTAB ('TriggerTester%')
GO

(Results)
TableName          TriggerName                    Status   
------------------ ------------------------------ -------- 
TriggerTester      TriggerTester_Update_demo      Disabled

-- Perform the update and see that LastUpdated and NOW are different
-- because the trigger was not run
UPDATE TriggerTester SET RowDesc = 'Test Row Updated Again'
SELECT *, getdate() NOW FROM TriggerTester
GO

(Results)
ID  RowDesc                LastUpdated             NOW                    
--- ---------------------- ----------------------- -----------------------
1   Test Row Updated Again 2004-01-30 02:29:17.403 2004-01-30 02:32:36.670

-- Enable the trigger
ALTER TABLE TriggerTester ENABLE TRIGGER TriggerTester_Update_demo
SELECT * FROM udf_Tbl_TriggerStatusTAB ('TriggerTester%')
GO

(Results)
TableName       TriggerName                    Status   
--------------- ------------------------------ -------- 
TriggerTester   TriggerTester_Update_demo      Enabled