如何在程序中启用/禁用触发器?

本文介绍了如何在程序中启用/禁用触发器?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在 MS SQL 服务器上有两个数据库(firstDB 和 secondDB).firstDB 中有过程 [dbo].[MaintaineIndexes]，secondDB 中有 DatabaseEventsTrigger.

I have two databases (firstDB and secondDB) on MS SQL server. There is procedure [dbo].[MaintaineIndexes] in firstDB and DatabaseEventsTrigger in secondDB.

我想在运行 [dbo].[MaintaineIndexes] 时禁用 secondDB DatabaseEventsTrigger.

I want to disable secondDB DatabaseEventsTrigger when I am running [dbo].[MaintaineIndexes].

所以我尝试了:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
DISABLE TRIGGER DatabaseEventsTrigger ON secondDB; 
...
ENABLE TRIGGER DatabaseEventsTrigger ON secondDB;
END;

当我运行程序时，我收到错误消息:找不到对象secondDB"，因为它不存在或您没有权限.";

When I'm running procedure I'm getting error message: "Cannot find the object "secondDB" because it does not exist or you do not have permissions.";

好的，然后我尝试了:

DISABLE TRIGGER DatabaseEventsTrigger ON ALL SERVER;

同样的错误.

仅使用此命令禁用触发器:

Disabling trigger only working with this commands:

USE [secondDB]
GO
DISABLE TRIGGER DatabaseEventsTrigger ON DATABASE;

但是我们不能在程序中使用USE"！那么，如何在我的情况下启用/禁用触发器?

But we can't use "USE" in procedures! So, how to enable/disable triggers in my situation?

推荐答案

使用动态 SQL:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
   EXEC('DISABLE TRIGGER DatabaseEventsTrigger ON secondDB'); 
   ...
   EXEC('ENABLE TRIGGER DatabaseEventsTrigger ON secondDB');
END;

或:

ALTER PROCEDURE [dbo].[MaintaineIndexes] 
AS
BEGIN
   EXEC('USE [secondDB]; DISABLE TRIGGER DatabaseEventsTrigger ON DATABASE;'); 
   ...
   EXEC('USE [secondDB]; ENABLE TRIGGER DatabaseEventsTrigger ON DATABASE;'); 
END;

这篇关于如何在程序中启用/禁用触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！