SQL Server 2008 MERGE语句-如何禁用INSTEAD OF INSERT触发器以允许MERGE [英] SQL Server 2008 MERGE statement - how to disable INSTEAD OF INSERT trigger to allow the MERGE

问题描述

我正在尝试在存储过程中使用SQL SERVER 2008 MERGE语句来更新/插入表. 我在表上有一个INSTEAD OF INSERT触发器，并且在尝试创建该过程时收到以下错误消息

I am attempting to use the SQL SERVER 2008 MERGE statement in a stored procedure for updating/inserting a table. I have an INSTEAD OF INSERT trigger on the table, and I get the following error message when attempting to CREATE the procedure

MERGE语句的目标电话"在某些情况下具有INSTEAD OF触发器， 但不是全部，在MERGE语句中指定的动作.在MERGE语句中，如果任何操作在目标上启用了INSTEAD OF触发器，则所有操作都必须启用INSTEAD OF触发器.

The target 'Phone' of the MERGE statement has an INSTEAD OF trigger on some, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled INSTEAD OF triggers.

我绝对不需要INSTEAD OF UPDATE触发器(并且由于在表上启用了CASCADE DELETES而无法创建一个).

I definitely do not need an INSTEAD OF UPDATE trigger, (and can't create one because of CASCADE DELETES being enabled on the table).

因此，在存储过程中，我首先在MERGE之前发出DISABLE TRIGGER命令.但是，当我运行存储的proc时，会遇到相同的错误，就像DISABLE TRIGGER命令永远不会运行一样.

So in the stored procedure I first issue a DISABLE TRIGGER command before the MERGE. But when I run the stored proc, I get the same error, as if the DISABLE TRIGGER command never gets run.

推荐答案

查询优化器对T-SQL批处理进行静态解析，并且一旦看到MERGE语句，它将验证需求.它不会考虑任何会影响MERGE语句之前的触发器的DDL语句.

The Query Optimizer does a static parse of your T-SQL batch, and as soon as it sees the MERGE statement, it will validate the requirements. It will NOT factor in any DDL statements that affect the triggers before the MERGE statement.

您可以使用GO来解决此问题，以将语句分成不同的批处理，但是如果在单个SP中(没有GO语句)，则有两种选择

You can work around this using GO to break the statements into separate batches, but if it is in a single SP (no GO statements), you have two choices

• 将MERGE放入主要调用的支持SP中；或
• 使用动态SQL

让我们创建一个带有触发器的表

Let's create a table with a trigger

create table tg1(i int)
;
create trigger tg1_tg on tg1 instead of insert as 
select 1
GO

然后尝试在桌子上合并

alter table tg1 disable trigger tg1_tg
;
merge tg1 as target
using (select 1 union all select 3) as source (X) on target.i = source.x
when matched then
    delete
when not matched by target then
    insert (i) values (x)
output $action, inserted.*, deleted.*
;
alter table tg1 enable trigger tg1_tg
;

不好.

MSG 5316，第16级，状态1，第1行
MERGE语句的目标"tg1"在MERGE语句中指定的某些(但不是全部)动作上具有INSTEAD OF触发器.在MERGE语句中，如果任何操作在目标上启用了INSTEAD OF触发器，则所有操作都必须启用INSTEAD OF触发器.

Msg 5316, Level 16, State 1, Line 1
The target 'tg1' of the MERGE statement has an INSTEAD OF trigger on some, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled INSTEAD OF triggers.

所以我们使用动态SQL

So we use dynamic SQL

alter table tg1 disable trigger tg1_tg
;
exec ('
merge tg1 as target
using (select 1 union all select 3) as source (X) on target.i = source.x
when matched then
    delete
when not matched by target then
    insert (i) values (x)
output $action, inserted.*, deleted.*
;')
alter table tg1 enable trigger tg1_tg
;

支持程序

让我们创建一个将执行MERGE的过程(生产过程可能会有一个表变量，使用#temp表或接受一些参数)

Support procedure

Let's create a procedure that will perform the MERGE (a production proc probably would have a table variable, use a #temp table or take in some parameters)

create proc tg1_MERGE as
merge tg1 as target
using (select 1 union all select 3) as source (X) on target.i = source.x
when matched then
    delete
when not matched by target then
    insert (i) values (x)
output $action, inserted.*, deleted.*
;
GO

不行...

MSG 5316，第16级，状态1，第1行
MERGE语句的目标"tg1"在MERGE语句中指定的某些(但不是全部)动作上具有INSTEAD OF触发器.在MERGE语句中，如果任何操作在目标上启用了INSTEAD OF触发器，则所有操作都必须启用INSTEAD OF触发器.

Msg 5316, Level 16, State 1, Line 1
The target 'tg1' of the MERGE statement has an INSTEAD OF trigger on some, but not all, of the actions specified in the MERGE statement. In a MERGE statement, if any action has an enabled INSTEAD OF trigger on the target, then all actions must have enabled INSTEAD OF triggers.

即使要创建触发器，您也需要禁用触发器-因此请禁用触发器并再次创建proc-它将在这次工作.

Even to create it, you need to disable the triggers - so disable the trigger and create the proc again - it will work this time around.

最后，您可以运行有效的批处理

Finally, you can run this batch which works

alter table tg1 disable trigger tg1_tg
;
exec tg1_MERGE
;
alter table tg1 enable trigger tg1_tg
;

这篇关于SQL Server 2008 MERGE语句-如何禁用INSTEAD OF INSERT触发器以允许MERGE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！