With the arrival of SQL Server 2005 the new OUTPUT operator was introduced. The OUTPUT operator works much like a "local trigger" on the current statement.
The drawback is that there is no way to filter the returned resultset directly. You have to insert the resultset in a staging table and work from there.

With SQL Server 2008 you now have a tool named Composable DML. What is then Composable DML?
Well, with this tool you can have a statement of UPDATE, DELETE and even MERGE as a data source for your query!

In this example I am showing you how to audit certain records when new values arrives from a file. All error-handling code has been removed for easier reading.

Thisi is how it can look like in SQL Server 2000

-- SQL Server 2000
DECLARE     @Audit TABLE
            (
                         CustomerID INT,
                         oldValue INT,
                         newValue INT
            )

DECLARE     @Work TABLE
            (
                         CustomerID INT,
                         Value INT
            )

INSERT      @Work
SELECT      1, 10000 UNION ALL
SELECT      2, 15000 UNION ALL
SELECT      3, 12000 UNION ALL
SELECT      4, 11000 UNION ALL
SELECT      5, 19000 UNION ALL
SELECT      6, 17000

DECLARE     @File TABLE
            (
                         CustomerID INT,
                         Value INT
            )

INSERT      @File
SELECT      3, 12000 UNION ALL
SELECT      4, 15000 UNION ALL
SELECT      5, 20000 

SELECT      *
FROM        @Work

BEGIN TRANSACTION

INSERT      @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
SELECT      w.CustomerID,
            w.Value,
            f.Value
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID
WHERE       w.Value <> f.Value

UPDATE      w
SET         w.Value = f.Value
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID

ROLLBACK TRANSACTION

SELECT      *
FROM        @Audit 

SELECT      *
FROM        @Work


As you can see, you have to use two statements if you don't want to use a TRIGGER.

This is how it can be done in SQL Server 2005 using same sample data preparation as above

-- SQL Server 2005
BEGIN TRANSACTION

UPDATE      w
SET         w.Value = f.Value
OUTPUT      deleted.CustomerID,
            deleted.Value,
            inserted.Value

INTO        @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
FROM        @Work AS w
INNER JOIN @File AS f ON f.CustomerID = w.CustomerID

DELETE      a
FROM        @Audit AS a
INNER JOIN @File AS f ON f.CustomerID = a.CustomerID
WHERE       a.oldValue = a.newValue

ROLLBACK TRANSACTION

SELECT      *
FROM        @Audit

SELECT      *
FROM        @Work

As you can see, you still need two statements if you don't want to use a TRIGGER. But using a trigger is still is two statements even if the trigger runs in same context.

Let us wrap this up and demonstrate the Composable DML available in SQL Server 2008, using same sample data preparation as above

-- SQL Server 2008
INSERT      @Audit
            (
                        CustomerID,
                        oldValue,
                        newValue
            )
SELECT      CustomerID,
            oldValue,
            newValue
FROM        (
                        UPDATE      w
                        SET         w.Value = f.Value,
                                    w.LastEdited = GETDATE()
                        OUTPUT      deleted.CustomerID,
                                    deleted.Value AS oldValue,
                                    inserted.Value AS newValue
                        FROM        @Work AS w
                        INNER JOIN @File AS f ON f.CustomerID = w.CustomerID
            ) AS d
WHERE       oldValue <> newValue

SELECT      *
FROM        @Audit

SELECT      *
FROM        @Work