HI, Pete,

Here's a sample trigger below. I created a shorter version, with the assumption your address table has Line1 and Line2, and your AddressHistory table (of address changes) has columns OldLine1, OldLine2, NewLine1, NewLine2, as well as the AddressID from the original Address ID row. Obviously you'd need to modify this if you had additional address line columns, but hopefully you get the idea.

When an update is issued on your original Address table, the trigger will fire - regardless of whether your stored procedure executes, or someone makes a manual update to the Address table. In SQL Server, the trigger fires once per update statement, regardless of whether the update affects 1 row or 100 rows.

The update trigger exposes two tables...INSERTED and DELETED...they basically come from the transaction log, have the same structure as the table from the original update (Address), and contain the "before/after" values for any rows affected by the update.

Since the original update could have affected multiple rows, the trigger code needs to JOIN the INSERTED and DELETED tables on the primary key (in this case, AddressID), to line up the "before/after" values. Note that the code also checks the before/after values for each column (in the WHERE clause), to only insert into the AddressHistory if a change actually happened.

Triggers are a tried-and-true way to log changes. They offer the benefit of firing every time, regardless of the process that performed the UPDATE. I make the analogy that triggers are like a good version of a "Big Brother" that sees everything that happens.

The problem with the OUTPUT statement is not so much with the statement itself, as OUTPUT is great for getting immediate feedback on an INSERT or UPDATE statement....but it requires EVERY process to either use your stored procedure, or use an OUTPUT statement, and that's not always practical to expect. A trigger is like a watchdog you can always count on.

Hope this helps...

CREATE TRIGGER AddressHistory_UpdateTrigger
on Address
AFTER UPDATE
AS
   BEGIN
       SET NOCOUNT ON;

       INSERT INTO AddressHistory (AddressID, OldLine1, OldLine2, NewLine1, NewLine2)    
             SELECT Inserted.AddressID, Deleted.Line1 AS OldLine1, Deleted.Line2 as OldLine2, 
                                     Inserted.Line1 AS NewLine1, Inserted.Line2 as NewLine2
                 FROM Inserted
                   JOIN Deleted on Inserted.AddressID = Deleted.AddressID
                      WHERE Inserted.Line1 <> Deleted.Line1 OR
                            Inserted.Line2 <> Deleted.Line2

   END
GO