Technical Article

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

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating