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
沒有留言:
發佈留言