How to use OUTPUT statement inside a SQL trigger?

Posted by Jeff Meatball Yang on Stack Overflow See other posts from Stack Overflow or by Jeff Meatball Yang
Published on 2010-05-13T22:11:14Z Indexed on 2010/05/13 22:14 UTC
Read the original article Hit count: 326

Filed under:
|

From MSDN:

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.

Can someone give me an example of this?

I'm not sure if this is correct:

create trigger 
MyInterestingTrigger
on TransactionalTable123
AFTER insert, update, delete
AS
            declare @batchId table(batchId int)
            insert SomeBatch (batchDate, employeeId)
            output SomeBatch.INSERTED.batchId into @batchId

            insert HistoryTable
            select b.batchId, i.col1, i.col2, i.col3
            from 
               TransactionalTable123.inserted i
            cross join @batchId b

© Stack Overflow or respective owner

Related posts about triggers

Related posts about output-clause