Microsoft SQL Server 2008 R2 Unleashed- P176:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1694 CHAPTER 43 Transact-SQL Programming Guidelines Tips and Tricks only in a trigger. The inserted and deleted tables provide access to the new old images of the modified rows this is similar to how they provide the information in triggers. In an INSERT statement you are allowed to access only the inserted table. In a DELETE statement you are allowed to access only the deleted table. In an UPDATE statement you are allowed to access both the inserted and deleted tables. Following is the general syntax of the OUTPUT clause UPDATE TOP expression PERCENT tablename SET column_name expression DEFAULT NULL variable expression variable column expression .n .n OUTPUT DELETED INSERTED from_table_name . column_name scalar_expression INTO @table_variable output_table column_list FROM table_name .n WHERE search_conditions DELETE TOP expression PERCENT tablename OUTPUT DELETED from_table_name . column_name scalar_expression INTO @table_variable output_table column_list FROM table_name FROM table_name .n WHERE search_conditions INSERT TOP expression PERCENT INTO tablename column_list OUTPUT INSERTED from_table_name . column_name scalar_expression INTO @table_variable output_table column_list VALUES DEFAULT NULL expression .n SELECT_statement The output table output_table may be a table variable permanent table or temporary table. If column_list is not specified the output table must have the same number of columns as the OUTPUT result set. If column_list is specified any omitted columns must either allow NULL values or have default values assigned to them. Any identity or computed columns in the output table must be skipped. In addition output_table cannot have any enabled triggers defined on it participate on either side of a foreign key constraint or have any check constraints or enabled rules. The OUTPUT Clause 1695 One use of the OUTPUT clause is to verify the rows being deleted updated or inserted begin tran delete from sales_big output deleted. where sales_id in select top
