December 14, 2006 at 10:21 am
In one of my stored procedure I am Inserting the data into 3 tables.
BEGIN TRANSACTION trans1
Insert into tbla(column1)
VALUES(@variable1)
Insert into tblb(columna)
VALUES(@variable2)
Insert into tblc(columnx)
VALUES(@variable3)
IF @@ERROR<>0
BEGIN
ROLLBACK TRANSACTION tran1
END
ELSE ROLLBACK TRANSACTION tran1
So in the above code if there is an error in inserting the data into table tbla I will be able to rollback the transaction. But if I get an error with sesond or third insert the first insert is suceesfull. BUt I want to have a transaction such that if an error occurs in any of the inserts all the threee inserts should not happen.
Thanks.
December 14, 2006 at 10:42 am
The problem is with the way you are using @@ERROR. It only returns the error for the last SQL statement that was executed. What you want to do is store the @@ERROR value into a variable after each step, and then handle that at the end. For instance, you could set a variable to 0, then add the value of @@ERROR to it after each step. If your variable is not equal to zero at the end, then you had a problem, and you can roll it all back.
December 14, 2006 at 11:07 am
if your using sql 2005 which i suspect you might be given your name! you should use try catch blocks which are much neater and much efficient as you dont have to explicitly check @@error after each atatement.
BEGIN try
begin transaction
Insert into tbla(column1)
VALUES(@variable1)
Insert into tblb(columna)
VALUES(@variable2)
Insert into tblc(columnx)
VALUES(@variable3)
end try
begin catch
ROLLBACK TRANSACTION
END catch
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply