在INSTEAD OF INSERT触发器的OUTPUT子句中，是否可以引用两个INSERTED表？ [英] In an OUTPUT clause in an INSTEAD OF INSERT trigger, is it possible to reference both INSERTED tables?

问题描述

SQL Server 2005 +

我有一个带有 INSTEAD OF INSERT 触发器的视图。在触发器主体内部，我想使用带有 OUTPUT 子句的语句，该子句引用两个 INSERTED 表： / p>

• INSSTEAD OF INSERT <的外部 INSERTTED 表触发器


• 输出 INSERTED 表>条款

MSDN 这样说：

如果在主体内部使用了包含OUTPUT子句的语句触发器，必须使用表别名来引用触发器插入和删除的表，以避免将列引用与与OUTPUT关联的INSERTED和DELETED表重复。

但是别名似乎不起作用：

 创建触发器v_insert ON v 
代替插入
作为开始
插入t（a，b，c）
插入的输出a，i nserted.b，external_inserted.d INTO t_prime（a，b，d）
 SELECT a，b，c 
 FROM作为外部插入的
插入
  

它产生错误无法绑定多部分标识符 outer_inserted.d。

解决方案

我读了它，因为在其中需要插入别名

在OUTPUT子句中的INSERTED只能 引用插入到t中的数据。

因此您的OUTPUT子句中不能包含 outer_inserted.d

您也无法做到这一点，这就是我的阅读方式

 插入到t（a，b，c）$ b中$ b已插入插入的输出.a，插入的.b INTO t_prime（a，b）
选择a，b，c 
 FROM插入的--no别名= ** FAIL ** 
  

SQL Server 2005+

I have a view with an INSTEAD OF INSERT trigger. Inside the body of the trigger, I want to use a statement with an OUTPUT clause which references both INSERTED tables:

• the outer INSERTED table for the INSTEAD OF INSERT trigger
• the inner INSERTED table for the OUTPUT clause

MSDN says this:

If a statement that includes an OUTPUT clause is used inside the body of a trigger, table aliases must be used to reference the trigger inserted and deleted tables to avoid duplicating column references with the INSERTED and DELETED tables associated with OUTPUT.

But aliasing doesn't seem to work:

CREATE TRIGGER v_insert ON v
INSTEAD OF INSERT
AS BEGIN
  INSERT INTO t (a, b, c)
  OUTPUT inserted.a, inserted.b, outer_inserted.d INTO t_prime (a, b, d)
  SELECT a, b, c
  FROM inserted as outer_inserted
END

It produces the error "The multi-part identifier "outer_inserted.d" could not be bound. Does that mean what I'm trying to do is not possible?

解决方案

I read it as the INSERTED alias would be required in the FROM where you access the trigger INSERTED.

The INSERTED in the OUTPUT clause can only reference the data inserted into t.

So you can't have outer_inserted.d in your OUTPUT clause

Nor can you do this, which is how I read it

INSERT INTO t (a, b, c)
  OUTPUT inserted.a, inserted.b INTO t_prime (a, b)
  SELECT a, b, c
  FROM inserted --no alias = **FAIL**

这篇关于在INSTEAD OF INSERT触发器的OUTPUT子句中，是否可以引用两个INSERTED表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！