OUTPUT your way to DML data change happiness, using the OUTPUT clause in your TSQL to capture inserted/deleted data. (Think trigger like features but without the trigger)

IT Pro Connection - The “SQL Guy” Post # 23: TRIGGER-less Tracking of Database Changes

"There are times when you need to track all the changes happening to a table. In the past, developers have used custom code to track changes by calling stored procedures, triggers and until recently even change data capture (also known as CDC).

SQL Server now supports a simplified method of auditing changes using DML statements without having to use Triggers or CDC (Change Data Capture). SQL Server introduces an OUTPUT clause as a part of DML statement that can help you in tracking changes made during any DML operations.

The OUTPUT clause can save the result-set in a table or table variable. The functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation.

Example:

In this example we will change the ITEMCOMMENT value from the ITEM table to the reverse of the original value and store the original and changed values in a table.

..."

OUTPUT Clause (Transact-SQL)

"Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

..."

The OUTPUT clause has only been around since SQL Server 2005... (sigh). Note to self: Remember this the next time you're hacking data.