2015年5月12日星期二

TSQL - s.p. to enable and disable all trigger

Create a s.p. which can enable and disable all trigger.
USE [ToolBox]
GO
/****** Object:  StoredProcedure [dbo].[u_AllTriggers]    Script Date: 05/12/2015 11:23:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[u_AllTriggers] 
(
@Database NVARCHAR (100),
@Disable bit
)
AS 
BEGIN 
----------------------------------------------------------------
-- DISABLE OR ENABLE ALL TRIGGERS
DECLARE @TRNAME nvarchar(100), @TANAME nvarchar(100), @STR nvarchar(300), @Script nvarchar(MAX)

SET @Script = 'DECLARE trigger_cursor CURSOR FOR ' +
'SELECT tr.name AS TRNAME, ta.name AS TANAME from ' +@Database+ '.sys.objects tr ' +
'join ' +@Database+ '.sys.objects ta on tr.parent_object_id = ta.object_id ' +
'where tr.type = ''tr'' ' +
'order by ta.name, tr.name '

EXECUTE sp_executesql @SCRIPT, N'@Database nvarchar(100)', @Database

PRINT @Script 

OPEN trigger_cursor

-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.

FETCH NEXT FROM trigger_cursor
INTO @TRNAME , @TANAME

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
IF @Disable=1 
BEGIN
SET @STR = 'USE ' +@DATABASE+'; DISABLE TRIGGER ' +@TRNAME+ ' ON ' +@TANAME
END 
ELSE 
BEGIN 
SET @STR = 'USE ' +@DATABASE+'; ENABLE TRIGGER ' +@TRNAME+ ' ON ' +@TANAME
END 

PRINT @STR
EXECUTE sp_executesql @STR

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM trigger_cursor
INTO @TRNAME, @TANAME
END

CLOSE trigger_cursor
DEALLOCATE trigger_cursor

-- EXEC [u_AllTriggers] @DATABASE = 'INPROASIA', @DISABLE=FALSE 
END  

沒有留言:

發佈留言