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 : 1049 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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 ?

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	-- 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

-- 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

0	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

Latest Posts