Programming Microsoft® SQL Server™ 2008

The INSERT OVER DML Syntax

SQL Server 2005 introduced the OUTPUT clause for INSERT, UPDATE, and DELETE statements as a way of capturing changed data resulting from those DML actions. Before and after snapshots of the changed data are exposed through the special INSERTED and DELETED pseudo-tables normally associated with triggers, so we don’t actually need to resort to triggers (which are best avoided, if possible) anymore to capture this information. As you saw in the preceding section, the new MERGE statement in SQL Server 2008 also supports the OUTPUT clause for this purpose. When used with MERGE, the OUTPUT clause can select from the INSERTED and DELETED pseudo-tables and also from a special virtual $action column of type nvarchar(10) that ...