/*
用途:暂停(开启)所有触发器
作者:Steven
参数:@flag = 1 开启, @flag = 0 暂停
*/
CREATE PROCEDURE P_set_trigger @flag bit AS
SET NOCOUNT ON
DECLARE @TableName sysname, @Action nvarchar(7), @SQLString nvarchar(500)
SET @Action = case when @flag = 1 then 'ENABLE' else 'DISABLE' end
DECLARE cTmp CURSOR FOR SELECT name FROM sysobjects WHERE xtype = N'U'
OPEN cTmp
FETCH cTmp INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' TRIGGER all'
EXEC sp_executesql @SQLString
-- Next
FETCH cTmp INTO @TableName
END
CLOSE cTmp
DEALLOCATE cTmp
if @flag = 1
PRINT 'Set all trigger to ENABLE'
else
PRINT 'Set all trigger to DISABLE'
SET NOCOUNT OFF