I am using the Transaction in sql to force all the query results in success.But It is not working

Question

I am using the Transaction in sql to force all the query results in success.But It is not working. What to do to set it so that all queries run successfully and if error occur in any query then all queries should not be executed. this is how I am using the transactions in sql server....

BEGIN TRANSACTION 
GO  
        INSERT [form].[control](Id,Name,Title,ElementType,IsRequired,Length,MinValue,MaxValue,Mask,DefaultValue,OptionType,DbType,AddOn,AddOnBefore,ShowHide,ShowHideCtrlType,DisabledCtrl,DisabledCtrlType,IsActive,IsHidden,ParentId,CreatedBy,CreatedOn,UpdatedBy,Updatedon,Class,Multiple) 
            VALUES('43',null,'Current Medical & Mental Health Diagnoses','2',null,null,null,null,null,null,null,'1',null,null,null,null,null,null,'1',null,null,'1',convert(datetime,'Jan  1 2016 12:00AM'),null,null,'15','0')
        update form.Control
        set class= NULL
        where id = 43

COMMIT TRANSACTION ;

Your script doesn't make sense to me. What's the point of inserting a new record and then updating it? Simply insert it with correct values to begin with. — Zohar Peled, Aug 17 '17 at 12:55
@kannan-kandasamy One record is updated and insertion is not performed. The record should also not be updated if error occur in insert statement. — Ali Nafees, Aug 17 '17 at 12:56
@ Zohar Peled This is just question related to Transaction that why transaction is not working. — Ali Nafees, Aug 17 '17 at 12:58
@ Zohar Peled Otherwise I can achieve that with many other ways — Ali Nafees, Aug 17 '17 at 12:58
[See How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) - you'll get a much better response if you can formulate a clear, concise, question. — Fraser, Aug 17 '17 at 18:52

score 2 · Accepted Answer · answered Aug 17 '17 at 13:00

2

Basically, what you do is something like this:

BEGIN TRANSACTION;
BEGIN TRY

-- Multiple sql statements goes here

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH

Note that the commit transaction must be the last statement in the try block. This way, the transaction is only committed if no statement inside the try block raised an error, and is rolled back only if there was an error.

answered Aug 17 '17 at 13:00

Zohar Peled

79,642
10
69
121

Nice I was not using the Begin Try – Ali Nafees Aug 17 '17 at 13:09

score 0 · Answer 2 · answered Aug 17 '17 at 13:00

0

Add at the begin:

SET XACT_ABORT ON;

Documentation

answered Aug 17 '17 at 13:00

Ruslan K.

1,912
1
15
18

score 0 · Answer 3 · answered Aug 17 '17 at 13:01

In SQL Server there is a setting called XACT_ABORT

It determines what happens when an error occurs inside a transaction. It defaults to off, which means that if you have multiple statements inside a transaction it can happen that one of them will fail, but others will succeed.

When set to ON all of the transaction is rolled back

Here is documentation from Microsoft with an example that looks like yours! https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql

I am using the Transaction in sql to force all the query results in success.But It is not working

3 Answers3

Linked