Sp to enable, disable or list all Triggers in  DB

,

This one is For SQL 2000.This SP 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.
Thanks  to Michael Broesdorf for his contribution.

Create Procedure sp_Trigger
@Db_Name Sysname = Null,
@Action Varchar (7) = 'List'
As
/*
** Author Rodrigo G. Acosta
** Email  acosta_rodrigo@hotmail.com
** Date 09/16/02
** sp_Trigger @Db_name=[Database Name], @Action=[Enable][Disable][List]  Default: List
** It Enable, Disable or List all triggers in the given database. For SQL2000
** Thanks to Michael Broesdorf for his contribution
*/

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 VarChar (250)
Set @Select = 'select 	Name As [Trigger],
			Object_Name(parent_obj) As [TableName],
			State=
				Case
					When (Status & 2048) >0  Then '+''''+'Disable'+''''+'
					When (Status & 2048) =0 Then '+''''+'Enable'+''''+'
				End
			From ' + @Db_Name + '.dbo.sysobjects where xtype = '+''''+'TR'+''''

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

Insert #Triggers
EXEC (@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 = 'Alter Table ' + @TableName + ' ' + @Action + ' Trigger All'
				EXEC (@cmd)
				Fetch Next From cTrigger Into @TableName
			End
	Close cTrigger
	Deallocate cTrigger
	End			


/*
** Shows the information of the triggers
*/
EXEC (@Select)

Rate

Share

Share

Rate