In the OUTPUT clause in the INSTEAD OF INSERT trigger, can I refer to both INSERTED tables?

Question

SQL Server 2005 +

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

• external INSERTED table for INSTEAD OF INSERT trigger
• INSERTED internal table for OUTPUT clause

MSDN says this :

If the statement containing the OUTPUT clause is used inside the trigger body, tables are inserted and deleted to reference the trigger to avoid duplicate column references with the INSERTED and DELETED tables associated with the OUTPUT.

But anti-aliasing does not 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 throws the error “Unable to associate an identifier with several parts of 'external_inserted.d.' Does this mean that what I'm trying to do is impossible?

Answer 1

I read it because the alias INSERTED is required in FROM, where you access the INSERTED trigger.

INSERTED in an OUTPUT clause can only refer to data inserted in t.

So you cannot have outer_inserted.d in your OUTPUT clause

And you cannot do it, that’s 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** 

Answer 2

It's a bit old, but you are missing the column d from the SELECT table of external_inserted, so you cannot reference it in OUTPUT.