有没有一种方法可以仅针对特定执行范围禁用SQL Server触发器？ [英] Is there a way to disable a SQL Server trigger for just a particular scope of execution?

问题描述

在SQL Server 2005中，是否可以通过触发器找到导致触发器触发的对象？我想用它为一个存储过程禁用触发器。

In SQL Server 2005, is there a way for a trigger to find out what object is responsible for firing the trigger? I would like to use this to disable the trigger for one stored procedure.

还有其他方法可以仅针对当前事务禁用触发器吗？我可以使用以下代码，但是如果我没记错的话，它也会影响并发事务-这将是一件坏事。

Is there any other way to disable the trigger only for the current transaction? I could use the following code, but if I'm not mistaken, it would affect concurrent transactions as well - which would be a bad thing.

DISABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

ENABLE TRIGGER { [ schema_name . ] trigger_name [ ,...n ] | ALL } ON { object_name | DATABASE | ALL SERVER } [ ; ]

如果可能，我想避免在表中使用 NoTrigger字段的技术并执行 NoTrigger = null ，因为我想使表尽可能的小。

If possible, I would like to avoid the technique of having a "NoTrigger" field in my table and doing a NoTrigger = null, because I would like to keep the table as small as possible.

原因我要避免使用触发器，因为它包含的逻辑对于手动更新表很重要，但是我的存储过程将处理此逻辑。因为这将是一个非常常用的过程，所以我希望它是快速的。

The reason I would like to avoid the trigger is because it contains logic that is important for manual updates to the table, but my stored procedure will take care of this logic. Because this will be a highly used procedure, I want it to be fast.

触发器会在服务器上增加额外的开销，因为它们会初始化隐式交易。一旦执行触发器，就会启动一个新的隐式事务，并且事务中的任何数据检索都将在受影响的表上保持锁定。

Triggers impose additional overhead on the server because they initiate an implicit transaction. As soon as a trigger is executed, a new implicit transaction is started, and any data retrieval within a transaction will hold locks on affected tables.

来自： http://searchsqlserver.techtarget.com/ tip / 1,289483，sid87_gci1170220,00.html＃trigger

推荐答案

我刚刚看到这篇文章最近在SQL Server Central通讯，它似乎提供了一种在连接上使用Context_Info可能会有用的方法：

I just saw this article recently highlighted on the SQL Server Central newsletter and it appears to offer a way which you may find useful using the Context_Info on the connection:

http://www.mssqltips.com/tip.asp?tip=1591

通过Terrapin编辑：

EDIT by Terrapin:

上面的链接包含以下代码：

The above link includes the following code:

USE AdventureWorks;  
GO  
-- creating the table in AdventureWorks database  
IF OBJECT_ID('dbo.Table1') IS NOT NULL  
DROP TABLE dbo.Table1  
GO  
CREATE TABLE dbo.Table1(ID INT)  
GO   
-- Creating a trigger  
CREATE TRIGGER TR_Test ON dbo.Table1 FOR INSERT,UPDATE,DELETE  
AS  
DECLARE @Cinfo VARBINARY(128)  
SELECT @Cinfo = Context_Info()  
IF @Cinfo = 0x55555  
RETURN  
PRINT 'Trigger Executed'  
-- Actual code goes here  
-- For simplicity, I did not include any code  
GO  

如果要防止执行触发器，可以执行以下操作：

If you want to prevent the trigger from being executed you can do the following:

SET Context_Info 0x55555 
INSERT dbo.Table1 VALUES(100)

这篇关于有没有一种方法可以仅针对特定执行范围禁用SQL Server触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！