有没有办法仅针对特定执行范围禁用 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

以上链接包含以下代码:

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屋！