/*
说明: 暂停/启用所有触发器及外键约束
*/
ALTER PROCEDURE [dbo].[P_set_trigger] @flag bit AS
SET NOCOUNT ON
DECLARE @TableName sysname, @ConstraintName sysname, @Action nvarchar(7), @SQLString nvarchar(500)
-- TRIGGER
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'
-- CONSTRAINT
SET @Action = case when @flag = 1 then 'CHECK' else 'NOCHECK' end
DECLARE cTmp CURSOR FOR SELECT object_name(parent_obj), name FROM sysobjects WHERE xtype = N'F'
OPEN cTmp
FETCH cTmp INTO @TableName, @ConstraintName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQLString = 'ALTER TABLE ' + @TableName + ' ' + @Action + ' CONSTRAINT ' + @ConstraintName
EXEC sp_executesql @SQLString
-- Next
FETCH cTmp INTO @TableName, @ConstraintName
END
CLOSE cTmp
DEALLOCATE cTmp
if @flag = 1
PRINT 'Set all Constraint to ENABLE'
else
PRINT 'Set all Constraint to DISABLE'
SET NOCOUNT OFF