SP to enable, disable or list all Triggers in  DB

,

SQL 7.0 and 2000 modified version of Rodrigo Acosta (racosta) Argentina Script that enable,disable or list all the Triggers in the given database. If enable or disable are specified, finds all the triggers of all the tables and enable or disable them, After that, it list all the triggers with it´s current state. If List is specified, then it only list the triggers with it current state.
Lots Credit to Rodrigo Acosta for his work.

Tim S

SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS  ON 
GO

IF OBJECT_ID('dbo.usp_Trigger') IS NULL
    EXEC  ('CREATE PROC dbo.usp_Trigger
        AS RAISERROR ( ''Dummy Proc Called'', 16, 62)')   
GO

ALTER PROC dbo.usp_Trigger

    @db_name    sysname     = NULL,
    @action     VARCHAR (7) = 'List'
AS
/*
** 
** Based on the code written by Rodrigo G. Acosta
** Modified by Tim Stahlhut Email stahta01 at netzero dot com
** 
** usp_trigger @db_name=[Database Name], @action=[Enable][Disable][List]  Default: List
** It Enable, Disable or List all triggers in the given database.
*/

SET NOCOUNT ON

/*
** If @Db_Name is not null, then checks that the database exists
*/
If @Db_Name Is Not Null
    Begin
        If Not Exists
            (Select name from master.dbo.Sysdatabases Where name=@Db_Name)
            Begin
                RaisError ('The Given database does not exists. Re-run the SP',16,1)
                Return
            End
    End

/*
** If the @Db_Name is null, then sets the SP to execute in the current Database
*/
If @Db_Name Is Null
    Begin
        Set @Db_Name=(Select db_name())
    End

/*
** If @action is different from Enable, Disable or List then raise the error
*/
If @Action Not In ('Enable', 'Disable', 'List')
    Begin
        RaisError ('Accepted parameters are Enable, Disable or List. Re-run the Sp',16,1)
        Return
    End

/*
** Inserts all the triggers name and other info into  the table #Triggers
*/
Declare @Select NVarChar (4000)
Set @Select = '
    USE ' + @db_name + '; SELECT    
        trg.name AS trigger_name, 
        USER_NAME(OBJECTPROPERTY ( tbl.id , ''OwnerId'')) + ''.'' + tbl.name AS table_name,
        CASE WHEN OBJECTPROPERTY ( trg.id , ''ExecIsTriggerDisabled'' ) = 1 THEN ''Disable''
             WHEN OBJECTPROPERTY ( trg.id , ''ExecIsTriggerDisabled'' ) = 0 THEN ''Enable''
             ELSE ''Unknown''
        END AS state
    FROM    dbo.sysobjects trg
    JOIN    dbo.sysobjects tbl ON trg.parent_obj = tbl.id
    WHERE   OBJECTPROPERTY ( trg.id, ''IsTrigger'') = 1 AND OBJECTPROPERTY ( tbl.id , ''IsMSShipped'' ) = 0 
    ORDER BY trg.name
'


Create Table #Triggers
    ([Trigger] Sysname, 
    TableName Sysname,
    State Varchar (7))

--  PRINT (@Select)
Insert #Triggers EXEC sp_executesql @stmt = @Select


/*
** With a Cursor executes the Action to enable or disable the Triggers
*/
If @Action Not In ('List')
    Begin
        Declare @TableName Sysname
        Declare @cmd Varchar (100)
        Declare cTrigger Cursor For
                    Select Distinct (TableName) from #Triggers
        
        Open cTrigger
        Fetch Next From cTrigger Into @TableName
        While @@Fetch_Status = 0    
            Begin
                Set @cmd = 'USE ' + @db_name + '; Alter Table ' + @TableName + ' ' + @Action + ' Trigger All'
                --  PRINT (@cmd)
                EXEC (@cmd)
                Fetch Next From cTrigger Into @TableName
            End
    Close cTrigger
    Deallocate cTrigger
    End         


/*
** Shows the information of the triggers
*/
--  PRINT (@Select)
EXEC sp_executesql @stmt = @Select

Rate

Share

Share

Rate