在存储过程中设置某表的触发器是否启用

在存储过程中设置某表的触发器是否启用

 
ALTER  PROCEDURE [dbo].[DisableEnable_Trigger]
@TableName varchar(50),
@DisableEnableType VARCHAR(10)
AS
/*****************************************************************************************
 **      Input:
**   @TableName: table to disable/enable trigger
**   @DisableEnableType in ('Disable', 'Enable')

 **   Exec DisableEnable_Trigger 'OuttblStore', 'Disable'
**   Exec DisableEnable_Trigger 'OuttblStore', 'Enable'
 **  
******************************************************************************************/
BEGIN
SET NOCOUNT ON
 --PRINT 'Executing DisableEnable_Trigger...' + @TableName
 DECLARE  @TriggerName  VARCHAR(100)
 DECLARE  @SqlStatement NVARCHAR(200)
 
 -- Get triggers list in cursor
 DECLARE  cursorTriggers CURSOR FOR
 SELECT name FROM sysobjects
  WHERE xtype = 'TR' and parent_obj =
   (SELECT id FROM sysobjects WHERE name = @TableName)

  OPEN cursorTriggers
   FETCH NEXT FROM cursorTriggers INTO @TriggerName
   WHILE @@FETCH_STATUS = 0
   BEGIN
   IF upper(@DisableEnableType) = 'DISABLE'
    SET @SqlStatement = 'ALTER TABLE '+@TableName+' DISABLE TRIGGER ' +@TriggerName
   ELSE
    SET @SqlStatement = 'ALTER TABLE '+@TableName+' ENABLE TRIGGER ' +@TriggerName
 
   --PRINT @SqlStatement
   EXEC sp_executesql @SqlStatement
    
      FETCH NEXT FROM cursorTriggers INTO @TriggerName
   END
   CLOSE cursorTriggers
   DEALLOCATE cursorTriggers

 --PRINT 'Executed DisableEnable_Trigger...' + @TableName+ '.'+@DisableEnableType
SET NOCOUNT OFF
END

 

posted on 2011-08-18 16:22 钻石眼泪 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/huanghai223/archive/2011/08/18/2144757.html