SQL Atomic Operation on UPDATE and DELETE

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

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