how to roll back a transaction ?

Question

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

Midhun T P · Answer

Hi as per my understanding you cannot rollback an query which is commited in the database. So it is better you use transactions and do rollback in case of any error - BEGIN TRAN Delete from tablename where id='1'; -- if delete is what you want then COMMIT TRAN -- if NOT then ROLLBACK Please check below links for more details - http://www.c-sharpcorner.com/UploadFile/63f5c2/commit-and-rollback-comands-in-sql-server/ http://www.codeproject.com/Questions/530141/HowplustoplusRollplusBackplusInsert-cUpdateplusan

Kavi Nithyasri · Answer

Thanks !

Vinay Singh · Answer

check the query syntex BEGIN TRAN @TransactionName INSERT INTO ValueTable VALUES(1) (2); ROLLBACK TRAN @TransactionName;

Bikesh Srivastava · Answer

Hi Kavi Visit here. https://msdn.microsoft.com/en-IN/library/ms181299.aspx

ketan borsdiya · Answer

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