CREATE TABLE [dbo].[HistoryLog]( [TableName] [char](120) NULL, [PrimaryKey] [int] NULL, [columnname] [varchar](150) NULL, [oldvalue] [varchar](120) NULL, [newvalue] [varchar](120) NULL, [DateTimeChanged] [datetime] NULL, [username] [varchar](120) NULL ) ON [PRIMARY]And suppose you wanted to track history when someone changes the Freight or Ship Date columns (I'll use the adventureworks PurchaseOrderHeader table as an example). There is only one "tried and true" way to capture if values actually changed. Use the INSERTED and DELETED tables...join them together on the key column of the source table, and examine the "after" and "before" values, like so:
CREATE TRIGGER [Purchasing].[PurchaseOrderHeader_UPDATE] ON [Purchasing].[PurchaseOrderHeader] AFTER UPDATE AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- if we have a table with multiple update triggers....they may step on each other, -- or they may wind up calling each other recursively, to the point of exceeding -- the sql server system limit on recursion (32 levels) update Purchasing.PurchaseOrderHeader set ModifiedDate= getdate() from inserted join Purchasing.PurchaseOrderHeader ON Inserted.PurchaseOrderID = PurchaseOrderHeader.PurchaseOrderID join Deleted on Inserted.PurchaseOrderID = Deleted.PurchaseOrderID where inserted.Freight <> deleted.Freight or inserted.shipdate <> deleted.shipdate -- Note...to check if something has been updated, and if the value -- has truly changed, don't use the UPDATED statement!....It only -- checks if the column was part of an UPDATE statement...not if it actually changed insert into HistoryLog -- all 1 statement SELECT 'Orders', Inserted.PurchaseOrderID, 'Freight', Deleted.Freight, Inserted.Freight, GETDATE(), SYSTEM_USER FROM Inserted JOIN Deleted ON Inserted.PurchaseOrderID = Deleted.PurchaseOrderID WHERE Inserted.Freight <> Deleted.Freight insert into HistoryLog SELECT 'Orders', Inserted.PurchaseOrderID, 'ShipDate', Deleted.ShipDate, Inserted.ShipDate, GETDATE(), SYSTEM_USER FROM Inserted JOIN Deleted ON Inserted.PurchaseOrderID= Deleted.PurchaseOrderID WHERE Inserted.ShipDate <> Deleted.ShipDate END go