A simple transaction in SQL
How to write a simple transaction in SQLTransaction 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.
posted by Gayani Devapriya @ 11:10 AM
1 Comments:
i was trying to undestand your blog. no luck.
Post a Comment
<< Home