Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL
Tip/Trick

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

Rate me:
Please Sign up or sign in to vote.
4.88/5 (6 votes)
13 Mar 2012CPOL 12.2K   10   1
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

SQL
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

 
SQL
 -- 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

 

SQL
-- 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

 
C++
-- 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)


Written By
Software Developer Banque Saudi Fransi
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Praisethanks Pin
Member 121728438-Jan-16 5:40
Member 121728438-Jan-16 5:40 
thanks for this tutorial

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.