CREATE TABLE dbo.MyMainTable
(
	MyCreateDate	datetime NOT NULL,
	MyUpdateDate	datetime NOT NULL,
	MyID		int NOT NULL IDENTITY (1, 1),	-- Primary key
	MyCol1		varchar(50) NULL,
	MyCol2		varchar(50) NULL,
	MyCol3		varchar(50) NULL,
	...
)  ON [PRIMARY]
GO

-- Default Constraints
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
	DF_MyMainTable_MyCreateDate DEFAULT GetDate() FOR MyCreateDate
GO
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
	DF_MyMainTable_MyUpdateDate DEFAULT GetDate() FOR MyUpdateDate
GO

-- Primary Key Constraint
ALTER TABLE dbo.MyMainTable ADD CONSTRAINT
	PK_MyMainTable PRIMARY KEY CLUSTERED 
(
	MyID
) ON [PRIMARY]
GO

CREATE TABLE dbo.MyMainTable_Audit
(
	A_MyAuditAction char(1) NOT NULL,
	A_MyAuditDate	datetime NOT NULL,

	A_MyCreateDate	datetime NOT NULL,
	A_MyUpdateDate	datetime NOT NULL,
	A_MyID		int NOT NULL,
	A_MyCol1	varchar(50) NULL,
	A_MyCol2	varchar(50) NULL,
	A_MyCol3	varchar(50) NULL
)  ON [PRIMARY]
GO

-- Unique Clustered Index
CREATE UNIQUE CLUSTERED INDEX IX_MyMainTable_Audit
	ON dbo.MyMainTable_Audit
(
	A_MyID,
	A_MyAuditDate
) ON [PRIMARY]
GO

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyMainTable
AFTER UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

	INSERT dbo.MyMainTable_Audit
	SELECT	[A_MyAuditAction] = CASE WHEN I.MyID IS NULL THEN 'D' ELSE 'U' END,-- D=Deleted, U=Updated
		[A_MyAuditDate] = GetDate(),
		D.*
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyID = D.MyID
			AND I.MyPrimaryKey2 = D.MyPrimaryKey2
			AND I.MyPrimaryKey3 = D.MyPrimaryKey3
			...
GO

SELECT	[Act],
	[Date],
	*	-- Or use an explicit column list MyID, MyCol1, MyCol2, ... 
FROM
(
	SELECT	[Act] = '*',
		[Date] = MyUpdateDate,	-- or use GetDate() to show "current value"
		*
	FROM	dbo.MyMainTable
	WHERE	    MyID = @MyID
		AND MyPrimaryKey2 = @MyPrimaryKey2
		AND MyPrimaryKey3 = @MyPrimaryKey3
		...
	UNION ALL
	SELECT	*
	FROM
	(
		SELECT TOP 100 *	-- First two columns are Action (Update/Delete) and AuditDate
		FROM	dbo.Audit_MyMainTableName
		WHERE	    A_MyID = @MyID
			AND A_MyPrimaryKey2 = @MyPrimaryKey2
			AND A_MyPrimaryKey3 = @MyPrimaryKey3
			...
		ORDER BY A_AuditDate DESC
	) AS X
) AS X
ORDER BY CASE WHEN [Act] = '*' THEN 1 ELSE 2 END, X.[Date] DESC, X.MyID, X.MyPrimaryKey2, X.MyPrimaryKey3, ...

CREATE TABLE dbo.MyMainTable_Audit
(
	A_MyAuditAction char(1) NOT NULL,
	A_MyAuditDate datetime NOT NULL,
	A_MyAuditID int IDENTITY(1,1) NOT NULL,

	A_MyCreateDate datetime NOT NULL,
	A_MyUpdateDate datetime NOT NULL,
	...

CREATE UNIQUE CLUSTERED INDEX IX_MyMainTable_Audit 
	ON dbo.MyMainTable_Audit 
(
	A_MyID ASC,
	A_MyAuditDate ASC,
	A_MyAuditID ASC
) ON PRIMARY

	INSERT dbo.MyMainTable_Audit
	(
		A_MyID, A_MyCol1, A_MyCol2, ...
	)
	SELECT	[A_MyAuditAction] = CASE WHEN I.MyID IS NULL THEN 'D' ELSE 'U' END,
		[A_MyAuditDate] = GetDate(),
		D.MyID, D.MyCol1, D.MyCol2, ...
	FROM	deleted D
		LEFT OUTER JOIN inserted I
			 ON I.MyID = D.MyID