Thanks Kevin.

Unfortunately the environment I'm working in a the moment is about as likely to let me create triggers as make me King of England.

But change tracking tips may be useful another day I'm sure.

>If you can't use CDC (i.e. you don't have SQL Enterprise), then you can always use triggers.
>
>For instance...suppose you have a table called HIstoryLog, where you track changes.
>
>

>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
>