Leo's solution is so similar to a solution I developed years ago for a number of my clients that I thought my code may have somehow been offered up to the public domain!!!
Paralleling Leo's article, I had even developed a couple of stored procedures that create
(1) the audit table(s) and
(2) the "change" triggers that post the modified row information to the audit table(s)...uncanny!
I create an audit ("change") table for each "production" table in the database. My audit method records both the "before" and "after" (a.k.a. "old" and "new") versions of all the columns of a modified production table row into the corresponding change table.
For example, given the following production table:
CREATE TABLE [dbo].[ProdTable] (
[Prod_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Prod_Col1] [varchar] (40) NULL ,
[Prod_Col2] [int] NULL ,
[Prod_Col3] [int] NOT NULL) ON [PRIMARY]
ALTER TABLE [dbo].[ProdTable] ADD
CONSTRAINT [PK_ProdTable] PRIMARY KEY CLUSTERED
([Prod_ID]) ON [PRIMARY]
I would have a corresponding change table looking like so:
CREATE TABLE [dbo].[Chg_ProdTable] (
[Chg_Trans_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Chg_Trans_Type] [char] (1) NOT NULL ,
[Chg_Trans_Date] [datetime] NOT NULL ,
[Chg_Trans_User_ID] [varchar] (50) NOT NULL ,
[Old_Prod_ID] [int] NULL ,
[New_Prod_ID] [int] NULL ,
[Old_Prod_Col1] [varchar] (40) NULL ,
[New_Prod_Col1] [varchar] (40) NULL ,
[Old_Prod_Col2] [int] NULL ,
[New_Prod_Col2] [int] NULL ,
[Old_Prod_Col3] [int] NULL ,
[New_Prod_Col3] [int] NULL) ON [PRIMARY]
ALTER TABLE [dbo].[Chg_ProdTable] ADD
CONSTRAINT [PK_Chg_ProdTable] PRIMARY KEY CLUSTERED
([Chg_Trans_ID]) ON [PRIMARY]
The first four columns of each of my audit tables records a unique transaction ID for the row, the type of transaction ("I"nsert, "D"elete, "U"pdate), the date/time stamp of the transaction, and the user ID of the person performing the change. My triggers query the inserted and deleted tables in order to pull the requisite data to be pumped into the audit table(s).
It's good to see that my work has been more-or-less validated by others...thanks!