Sp to enable, disable or list all Triggers in a DB (SQL 7)

,

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.

I didn't mentioned before, so I apologize for the mail asking the version. For all the ones that asked, I'm posting the SQL 2000 version.

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.
*/

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 = 3089 Then "Disable"
					When Status = 1041 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