Output Clause - SQL SERVER, You Can Replace Some of Your Triggers

Introduction

We can use the output clause instead of triggers, while doing the transaction. It gives you more readability in the query itself, and no need of triggers. By seeing the example you can understand more. I found its useful and would like to share it with all you people.Thanks for Microsoft for such a great functionality in SQL Server 2008

Background

http://msdn.microsoft.com/en-us/library/ms177564.aspx

Using the Code

Better to explain by small case scenarios. Please run the below Query to create test tables

GO
 
/****** Object:  Table [dbo].[StudentUpdatedLog]    Script Date: 03/04/2012 16:04:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentUpdatedLog](
	[StudentID] [numeric](18, 0) NULL,
	[StudentName] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[UpdatedDateTime] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StudentInsertedLog]    Script Date: 03/04/2012 16:04:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentInsertedLog](
	[StudentID] [numeric](18, 0) NULL,
	[StudentName] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[InsertedDateTime] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[StudentDeletedLog]    Script Date: 03/04/2012 16:04:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StudentDeletedLog](
	[StudentID] [numeric](18, 0) NULL,
	[StudentName] [nvarchar](50) NULL,
	[Age] [int] NULL,
	[DeletedDateTime] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 03/04/2012 16:04:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
	[StudentID] [numeric](18, 0) NULL,
	[StudentName] [nvarchar](50) NULL,
	[Age] [int] NULL
) ON [PRIMARY]
GO

 

Scenario1 : Track the insertion operation to a particular log table

 

 -- step 1 : Insert operation
INSERT INTO [Student]
           ([StudentID]
           ,[StudentName]
           ,[Age])
output inserted.StudentID,inserted.StudentName,inserted.Age ,GETDATE() into StudentInsertedLog
VALUES  (1,'sabin',26)
GO

 

-- step 2 : Check the values in Student table and StudentInsertedLog  table
select * from [Student]
select * from StudentInsertedLog
go

 

Scenario2 : Track the update operation to a particular log table

 

-- step 1 : update operation
update   [Student] 
set StudentName = 'sabindas' 
output inserted.StudentID,deleted.StudentName,inserted.Age ,GETDATE() into StudentUpdatedLog
where StudentID=1
 
 
-- step 2 : Check the values in Student table and StudentUpdatedLog  table
select * from [Student]
select * from StudentUpdatedLog
go

 

Scenario3 : Track the delete operation to a particular log table

 

 -- step 1 : update operation
delete   [Student] 
output deleted.StudentID,deleted.StudentName,deleted.Age ,GETDATE() into StudentdeletedLog
where StudentID=1 
 
-- step 2 : Check the values in Student table and StudentdeletedLog  table
select * from [Student]
select * from StudentdeletedLog
go 

Thanks ,