Click here to Skip to main content
15,868,141 members
Articles / Database Development
Tip/Trick

SQL Atomic Operation on UPDATE and DELETE

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
13 Jan 2012CPOL1 min read 46.2K   6   1
How to do SQL atomic operations on UPDATE and DELETE syntax.

Introduction


SQL atomic operation is used to combine several operations in one operation. Thus when one operation has failed, other operations will also be failed. Examples of SQL atomic operation usage can be seen on UPDATE and DELETE operations.


In normal code, developers tend to get the record values before and after UPDATE operation. The steps will be:


  • SELECT the values before UPDATE operation
  • Do UPDATE operation
  • SELECT the values after UPDATE operation.

With SQL atomic operation, these three steps can be combined into one step only as we can see in the code sample below.


Another example of SQL atomic operation usage is to get the record values before DELETE operation. Normal code steps will be:


  • SELECT the values before DELETE operation
  • Do DELETE operation

Same with UPDATE operation, with SQL atomic operation these two steps can be combined into one step only as we can see in the code sample below:


SQL Atomic Operation


SQL
--Declare variable table
DECLARE @Students TABLE
(
	StudentID int,
	Name nvarchar(50)
)

--Insert testing data
INSERT INTO @Students(StudentID, Name) 
VALUES(1,'Jack')

--Atomic update on data
UPDATE @Students
SET Name = 'Ray'
OUTPUT @@error as ErrorCode, DELETED.Name as DeletedName, INSERTED.Name as InsertedName
WHERE StudentID = 1

--Atomic delete on data
DELETE FROM @Students
OUTPUT @@error as ErrorCode, DELETED.Name as DeletedName
WHERE StudentID = 1

As we can see in the code above, inside UPDATE operation there is OUTPUT keyword. This OUTPUT keyword is equal to SELECT keyword. So in one execution, we get UPDATE and SELECT operations executed together.


Basically UPDATE operation is combining DELETE and INSERT operations. As we can see in the code above, there are other keywords called DELETED and INSERTED. DELETED is used to get deleted record values, while INSERTED is used to get inserted record values.


As for DELETE operation, only DELETED keyword can be used to get deleted record data.


Hints


This SQL atomic operation is very useful for user action log where we need to get the data values before and after user action in our application.

License

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


Written By
Software Developer
Singapore Singapore
Robby Tendean had his Master Degree in Computer Science from National Taiwan University of Science and Technology. His master degree thesis, Energy-Efficient Routing Protocol for Wireless Sensor Networks with Static Clustering and Dynamic Structure, has been published in Springerlink International Journal.

Currently he is working as Software Engineer based in Singapore with several years experience in HTML, Javascript, JQuery, C#.NET, VB.NET, Microsoft SQL Server for web development.

Comments and Discussions

 
QuestionSQL Atomic Operation on UPDATE and DELETE Pin
winstonmacmahon18-Sep-14 4:31
winstonmacmahon18-Sep-14 4:31 

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.