CODEWITHSUNDEEP
sqlsql-serversql-server-2008
TRS
TRS

Reputation: 490

manage data flow with SQL tables

I'm going to insert data to 3 tables.(According to my process.).But data must be entered into three tables.other wise i want to rollback transaction. Eg:- if data inserted into 2 tables and didn't insert table 3, i want to delete entered values from the 2 tables.

What is the best way to do this.can i use trigger to do that? Please help me to solve this.

Upvotes: 0

Views: 56

Answers (2)

praveen
praveen

Reputation: 12271

U can try this :

SET XACT_ABORT ON
 BEGIN TRY
BEGIN TRAN
INSERT INTO Table1 values ()
INSERT INTO Table2 values ()
INSERT INTO Table3 values ()
COMMIT TRAN
END TRY

BEGIN CATCH 
raiserror('Custome Error Message ',16,20)
END CATCH
SET XACT_ABORT OFF

Using SET XACT_ABORT ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back

In the catch block either you can raise a custom error or log it into a table

 Begin CATCH
 Insert into #LogError values (1,'Error while inserting a duplicate value')
 if @@TRANCOUNT >0
 rollback tran 
 END CATCH

Upvotes: 3

yogi
yogi

Reputation: 19619

Use transactions

Commit tran -- if success
Rollback tran -- on error

Something like this

Create Proc YourSPName
AS

BEGIN TRAN

INSERT INTO TableOne ( -- Columns)
VALUES  ( -- Values)

IF (@@ERROR <> 0) goto Truncater;

INSERT INTO TableTwo ( -- Columns)
VALUES  ( -- Values)

IF (@@ERROR <> 0) goto Truncater;

INSERT INTO TableThree ( -- Columns)
VALUES  ( -- Values)

IF (@@ERROR <> 0) goto Truncater;


Truncater:
if(@@ERROR<>0)
begin
    rollback tran
    return -1 -- error flag
end
else
    commit tran   

 GO

For more Go here.

Upvotes: 1

Related Questions