How to use OUTPUT statement inside a SQL trigger?
- by Jeff Meatball Yang
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
