Jim,
I'm not aware of any built-in functionality, but I work with SQL from an application angle, and am less familiar with built-in administration capability to retrieve information from transaction logs (and maybe someone here has worked with it and can help).
Here's a very basic example of an update trigger for logging a customer address change to an application log. It assumes a customer table with CustKey (int), Address (char), LastUpdate (d/t), and a generic application log with tablename (char), logkey (int), colname (char), oldval (char), and newval (char)...
CREATE TRIGGER TR_UPD_CUSTOMER ON dbo.CUSTOMER
FOR UPDATE
AS
DECLARE @nRowsChanged int
DECLARE @cOldAddr CHAR(40)
DECLARE @cNewAddr CHAR(40)
DECLARE @nCustKey Integer
DECLARE @dLastUpdate DateTime
SET @dLastUpdate = GETDATE()
SET @nRowsChanged = @@ROWCOUNT
IF @nRowsChanged > 0 --was at least one row updated?
BEGIN
-- update the date/time stamp in customer (assuming you use a lastupdate)
UPDATE CUSTOMER SET LASTUPDATE=@dLastUpdate FROM CUSTOMER WHERE CustKey = @nCustKey
-- grab the oldvalue and newvalue, and also get the unique customer key
SET @cOldAddr = (SELECT Address FROM Deleted)
SET @cNewAddr = (SELECT Address FROM Inserted)
SET @nCustKey = (SELECT CustKey FROM Inserted)
IF @cOldAddr <> @cNewAddr -- compare, and insert into an application DB log
BEGIN
INSERT INTO DBLog (TableName, LogKey, ColName, OldVal, NewVal)
VALUES ('CUSTOMER', @nCustKey, 'Address',@cOldAddr, @cNewAddr)
END
END
One note...in SQL Server, if multiple rows are updated in one UPDATE statement, the update trigger fires ONCE, not one for every row. So you'd need to restructure things in the querying of INSERTED and DELETED to make sure you're doing it one row at a time.
(In my example, I'm querying address from INSERTED and DELETED directly into a variable, on an assumption that only one row would ever be updated at a time. That would generate an error if someone did a mass UPDATE of multiple rows in one statement, because INSERTED/DELETED have multiple rows. So again, you'd need to loop through for each custkey that's in INSERTED, unless there's a better way to do it.)
This is just a stripped down version of an application logging function that I use, but hopefully it should give you some ideas. I write additional info to the log (who the user was, what time did it occur, etc.) I also made it generic, as I loop through the columns in a data dictionary table as opposed to listing them out (which wound up being a real adventure, because I had to get into dynamic sql).
But hopefully this should give you an idea.
Kevin