Rollback inserted data from 4 tables if one table got error while insertion in Sql Server [Resolved]

Posted by Kasani007 under Sql Server on 8/3/2014 | Points: 10 | Views : 502 | Status : [Member] | Replies : 3
in SQL Server, If I have 4 tables, If I insert some data in 4 tables at a time. if one table got error and not inserted and remaining 3 tables got inserted should be roll back ? how can we rollback the data in other 3 tables ?




Responses

Posted by: vishalneeraj-24503 on: 8/3/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Hi,

You can Use Transaction if your Insert Statement is inside Storedprocedure.
Or you can use SQLTransaction class in Code Behind.
In Transaction,If your 1 statement fails,then all your operations will also be failed.
You can write Rollback transacton in catch block.

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 8/4/2014 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
-- Sample Code for proving your requirement


-- Without any ERROR
CREATE TABLE T1 ( ID INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE T2 ( ID INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE T3 ( ID INT IDENTITY(1,1), name VARCHAR(10))

BEGIN TRY
BEGIN TRAN t1
INSERT INTO t1 values('AAA')
INSERT INTO T2 values('BBB')
INSERT INTO T3 values('CCC')
Commit Tran t1
END TRY
BEGIN CATCH
ROLLBACK TRAN t1
END CATCH

SELECT * FROM t1
SELECT * FROM T2
SELECT * FROM T3

DROP TABLE t1
DROP TABLE T2
DROP TABLE T3

GO


-- with ERROR
CREATE TABLE T1 ( ID INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE T2 ( ID INT IDENTITY(1,1), name VARCHAR(10))
CREATE TABLE T3 ( ID INT IDENTITY(1,1), name VARCHAR(10))


BEGIN TRY
BEGIN TRAN t1
INSERT INTO t1 values('DDD')
INSERT INTO T2 values('EEE BBB BBB EEE') -- the value exceeds column length (10). So it should rollback all INSERTs
INSERT INTO T2 values('CCC')
Commit Tran t1
END TRY
BEGIN CATCH
ROLLBACK TRAN t1
END CATCH

SELECT * FROM t1
SELECT * FROM T2
SELECT * FROM T3

DROP TABLE t1
DROP TABLE T2
DROP TABLE T3


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rasikbapotra on: 8/7/2014 [Member] Starter | Points: 25

Up
0
Down
Use Try Catch with all statement under Try with Commit at the end of Try block and Rollback in Catch block if error occurs.

Kasani007, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response