how to roll back a transaction ?

Kavi Nithyasri

10y861SQL

write a query to roll back transaction (say delete a record) in sql server 2012 ?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

check the query syntex

BEGIN TRAN @TransactionName

INSERT INTO ValueTable VALUES(1), (2);

ROLLBACK TRAN @TransactionName;

Bikesh Srivastava10y ago
Hi Kavi,
Visit here.
https://msdn.microsoft.com/en-IN/library/ms181299.aspx
0
ketan borsdiya10y ago
Hi Kavi,
Write your procedure below format,
Create PROCEDURE [dbo].[PRC_Test]
AS
BEGIN
SET XACT_ABORT ON
SET NOCOUNT ON

BEGIN TRY
DECLARE @checkError Bit = 0

IF(@@TRANCOUNT = 0)
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRANSACTION
SET @checkError = 1
END

--Write Your logic here

IF (@@TRANCOUNT > 0 AND @checkError = 1)
BEGIN
IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
END
END

END TRY

BEGIN CATCH
IF (@@TRANCOUNT > 0 AND @checkError = 1)
BEGIN
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION
END
END
END CATCH

END
0