There is no generic way to do it the way you want. Ultimately you end up writing reams of code for each table. Not to mention it can be fairy slow if you need to compare each column for change.
Also the fact that you might be updating multiple rows at the same time implies you need to open a cursor to loop through all the records.
The way I'd do it will be using table with structure identical to the tables you are tracking and unpivot it later to show which columns have actually changed. I'd also keep track of the session that actually did the change. This assumes that you have primary key in the table being tracked.
So given a table like this
CREATE TABLE TestTable
(ID INT NOT NULL CONSTRAINT PK_TEST_TABLE PRIMARY KEY,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40))
I'd create an audit table like this in the audit schmea.
CREATE TABLE Audit.TestTable
(SessionID UNIQUEIDENTIFER NOT NULL,
ID INT NOT NULL,
Name1 NVARCHAR(40) NOT NULL,
Name2 NVARCHAR(40),
Action NVARCHAR(10) NOT NULL CONSTRAINT CK_ACTION CHECK(Action In 'Deleted','Updated'),
RowType NVARCHAR(10) NOT NULL CONSTRAINT CK_ROWTYPE CHECK (RowType in 'New','Old','Deleted'),
ChangedDate DATETIME NOT NULL Default GETDATE(),
ChangedBy SYSNHAME NOT NULL DEFAULT USER_NAME())
And a trigger for Update like this
CREATE Trigger UpdateTestTable ON DBO.TestTable FOR UPDATE AS
BEGIN
SET NOCOUNT ON
DECLARE @SessionID UNIQUEIDENTIFER
SET @SessionID = NEWID()
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','Old',@SessionID FROM Deleted
INSERT Audit.TestTable(Id,Name1,Name2,Action,RowType,SessionID)
SELECT ID,name1,Name2,'Updated','New',@SessionID FROM Inserted
END
This runs quite fast. During reporting , you simply join the rows based on sessionID, and Primary key and produce a report. Alternatively you can have a batch job that periodically goes through all the tables in the audit table and prepare a name-value pair showing the changes.
HTH