Click here to Skip to main content
Click here to Skip to main content
Go to top

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

, 13 Mar 2012
Rate this:
Please Sign up or sign in to vote.
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 ,

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

sabindas k s
Software Developer Banque Saudi Fransi
India India
No Biography provided
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140922.1 | Last Updated 13 Mar 2012
Article Copyright 2012 by sabindas k s
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid