Using DML Triggers That Include OUTPUT
You can create DML triggers with an INSERT, UPDATE, DELETE, or MERGE statement to return rows modified by the trigger action. However, the OUTPUT clause must specify a table variable to receive the rows (using an INTO clause) under the following circumstances, otherwise the trigger will fail:
The trigger is fired as part of multiple queries inside a single transaction.
The trigger accesses external data.
If the trigger fails, the triggering action fails as well, and the transaction containing the triggering action rolls back.
This consideration only applies to an INSERT, UPDATE, DELETE, or MERGE statement that executes inside of a DML trigger, and includes an OUTPUT clause that does not specify INTO. It does not apply to an OUTPUT clause inside an INSERT, UPDATE, DELETE or MERGE statement that causes a DML trigger to fire.