MDJ - That wasn't exactly what I had in mind. More like this (ignoring the error handling in yours which I recommend anyway to be safe):

CREATE TABLE Main

(ID INT IDENTITY PRIMARY KEY

,C1 DATETIME

,C2 VARCHAR(10)

,C3 INT

,UserID VARCHAR(20)) -- Last user who updated the record

GO

CREATE TABLE Audit

(TableName VARCHAR(20)

,ColumnName VARCHAR(20)

,RecordID INT

,UserID VARCHAR(20)

,UpdatedDate VARCHAR(10)

,OldValue VARCHAR(100)

,NewValue VARCHAR(100))

GO

CREATE TRIGGER [dbo].[tr_Audit] ON Main

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

SET NOCOUNT ON;

INSERT INTO Audit

SELECT '#Main', ColName, CASE WHEN i.ID IS NULL THEN d.ID ELSE i.ID END

,CASE WHEN i.UserID IS NULL THEN d.UserID ELSE i.UserID END

,GETDATE(), OldValue, NewValue

FROM INSERTED i

FULL OUTER JOIN DELETED d ON i.ID = d.ID

CROSS APPLY (

VALUES ('C1', CONVERT(VARCHAR(27), d.C1, 113), CONVERT(VARCHAR(27), i.C1, 113))

,('C2', d.C2, i.C2)

,('C3', CAST(d.C3 AS VARCHAR(20)), CAST(i.C3 AS VARCHAR(20)))

) a(ColName, OldValue, NewValue)

-- You may want to refine this WHERE just a bit.

WHERE ISNULL(OldValue,'') <> ISNULL(NewValue,'')

END

GO

INSERT INTO Main

SELECT GETDATE()-1, 'A VALUE', 42, 'Dwain.C'

UNION ALL SELECT GETDATE()-2, 'B VALUE', 40, 'Dwain.C'

UNION ALL SELECT GETDATE()-2, 'C VALUE', 40, 'Dwain.C'

UNION ALL SELECT GETDATE()-2, 'D VALUE', 40, 'Dwain.C'

UPDATE a

SET C2='X VALUE', UserID='MyDoggieJessie'

FROM Main a

WHERE ID IN (2, 3)

DELETE FROM Main

WHERE ID = 4

-- Add an update to the audit trail on any DELETE

-- to capture the deleting user

UPDATE Audit SET UserID = 'MyCatHobbsy'

WHERE RecordID = 4 AND NewValue IS NULL

SELECT * FROM Main

SELECT * FROM Audit

DROP TRIGGER [dbo].[tr_Audit]

DROP TABLE Main

DROP TABLE Audit

Note that I forgot to include the primary key value in the list of fields to store in my original post.

Notice how the trigger workds for INSERTs, UPDATEs and DELETEs. It is possible you don't want to log all of these events, so for example:

1. INSERT and UPDATE only - Use a LEFT JOIN of INSERTED to DELETED (INSERTED will always have rows but DELETED will not on INSERT).

2. UPDATE and DELETE only - use a LEFT JOIN of DELETED to INSERTED (DELETED will always have rows but INSERTED will not on DELETE).

etc.

Edit: Silly me! Forgot the WHERE clause in the trigger.