Click here to Skip to main content
12,501,207 members (40,617 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

7.5K views
10 bookmarked
Posted

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

, 13 Mar 2012 CPOL
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

You may also be interested in...

Comments and Discussions

 
Praisethanks Pin
Member 121728438-Jan-16 5:40
memberMember 121728438-Jan-16 5:40 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160919.1 | Last Updated 13 Mar 2012
Article Copyright 2012 by sabindas k s
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid