There's something out there in life that will delight and amaze you.
Get up, get moving, and go find out what it is. - (Ralph Marston)

Friday, March 17, 2006

A simple transaction in SQL

How to write a simple transaction in SQL

Transaction is a set of SQL commands that need to be executed in order to fulfill a batch of activities relevant to a database.
Let’s look at an example.
In simple terms, if we were to delete a record; which is a foreign key field of another table; that also need to be deleted at the same time; this could be considered as a set of activities that need to be performed in one attempt.
So ideal would be to go for a transaction.

The purpose: Once I delete a record in the Orders table the corresponding entry should be deleted in the Order Details table as well.

Create procedure DeleteOrder
(
@OrderID int
)
as

Declare @err1 int,@err2 int

-- start transaction
Begin tran

-- execute the delete
Delete from Orders
Where [OrderID] = @OrderID
Select @err1 = @@Error

Delete from OrderDetails
Where [OrderID] = @OrderID
Select @err2 = @@Error

If (@err1 = 0) and (err2 = 0)
Begin
-- commit the transaction
Commit tran
End
Else
Begin
-- rollback the transaction
Rollback tran
End



What is @@Error?

This contains the error ID from the finally executed SQL statement. So in other words, this is an indication whether the last SQL statement was executed successfully or not. If it has executed successfully, the value of @@Error will be 0.


So at the end we could check the value of the variable which the @@Error was saved and based on that; commit or rollback.

1 Comments:

At 7:58 PM, Blogger SIYAM said...

i was trying to undestand your blog. no luck.

 

Post a Comment

<< Home