Controlling Transactions

prashantSum's Avatar
Contributor
Hi all,
I want to controll transactions through query analyzer or stored procedure.
I thought it can be controlled with 'begin transaction' and 'commit' or 'commit transaction' but I am unable achieve with it.

I have two statements in my stored procedure first one is insert and next one is delete, even if insert is failing delete is getting executed successfully, so how can we controll transactions in MS SQL?
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Why is the insert failing.
prashantSum's Avatar
Contributor
I was just testing with it, so I am trying to insert the a record with the same primary key.
But what ever may be the case, I think transactions should be Atomic.

I even tried to set auto commit false with
set auto-commit = false
but it's giving syntax error
shabbir's Avatar, Join Date: Jul 2004
Go4Expert Founder
Transaction is not that way atomic. Transaction gurantees that no other operation can occur in between your transaction on a particular record.