Do you guys have any articles or links you can point me to on server side traces? I can't seem to find how to trace a change to a specific table.column.

Here is an example of my trigger using Northwind.Orders as an example. Feel free to critique this as well. Does my code to get hostname, username, etc seem valid?

--Northwind - Trigger to track changes to Orders.OrderDate

--Create table store log data

CREATE TABLE [Orders_Log] (

[ID] [int] IDENTITY (1, 1) NOT NULL ,

[OrderID] [int] NOT NULL ,

[orig_OrderDate] [datetime] NULL ,

[new_OrderDate] [datetime] NULL ,

[Computer_Name] [varchar] (80) NULL ,

[Windows_user] [varchar] (80) NULL ,

[sql_login] [varchar] (80) NULL ,

[program_Name] [varchar] (80) NULL ,

[command] [varchar] (80) NULL ,

[MAC_address] [varchar] (80) NULL ,

[log_date] [datetime] NOT NULL DEFAULT (getdate())

) ON [PRIMARY]

GO

--Trigger to track changes to OrderDate

--drop trigger [track_OrderDate_change]

CREATE TRIGGER [track_OrderDate_change] ON [dbo].[Orders]

For UPDATE

AS

If Update(OrderDate)

Begin

Set Nocount On

insert into [Orders_Log]

(OrderID, orig_OrderDate, new_OrderDate,

Computer_Name, Windows_User, sql_login, program_name, command, mac_address)

select d.OrderID, d.OrderDate, i.OrderDate,

(SELECT TOP 1 hostname FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid ),

(SELECT TOP 1 nt_username FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid ),

(SELECT TOP 1 loginame FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid ),

(SELECT TOP 1 program_name FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid ),

(SELECT TOP 1 cmd FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid ),

(SELECT TOP 1 net_address FROM [master].[dbo].[sysprocesses] WHERE spid = @@spid )

from [inserted] i, [deleted] d

Where i.OrderID = d.OrderID

End