Using OUTPUT clause for auditing

In SQL Server 2000 and earlier versions, trigger was used to log changes into a table for audit purpose. But the OUTPUT clause introduced in SQL Server 2005 can also be used to perform such logging. Let us see by an example:

CREATE TABLE [dbo].[MyTable1](

[Col1] [int] NULL,

[Col2] [varchar](10) NULL

) ON [PRIMARY]

CREATE TABLE [dbo].[MyTable1_Audit](

[Col1_Old] [int] NULL,

[Col1_New] [int] NULL,

[Col2_Old] [varchar](10) NULL,

[Col2_New] [varchar](10) NULL,

[Action] varchar(50) NULL

) ON [PRIMARY]

GO

INSERT INTO MyTable1

OUTPUT NULL, inserted.Col1, NULL, inserted.Col2, 'INSERT' 

INTO [MyTable1_Audit]

VALUES (1,'WOW')

SELECT * FROM MyTable1

SELECT * FROM [MyTable1_Audit]

UPDATE MyTable1 SET Col1 = 10 , Col2 = 'Great' 

OUTPUT deleted.Col1, inserted.Col1, 

deleted.Col2, inserted.Col2, 'UPDATE' INTO [MyTable1_Audit]

WHERE Col1 = 1

SELECT * FROM MyTable1

SELECT * FROM [MyTable1_Audit]