Why a transaction failed? How can we ensure a transaction work?

[9295]

I have a stored procedure like the following (in between the two ----- lines):
------------------------------------
CREATE PROCEDURE abc
@V1 int,
@V2 money,
@V3 int output
AS
……...
……...
Begin tran
Insert or Update data Statement-1
IF (@@ERROR <> 0) GOTO ROLL
……...
Insert or Update data Statement-2
IF (@@ERROR <> 0) GOTO ROLL
……...
Insert or Update data Statement-3
IF (@@ERROR <> 0) GOTO ROLL -- Never reach here
……...
Insert or Update data Statement-4
IF (@@ERROR <> 0) GOTO ROLL
……...
Insert or Update data Statement-5
IF (@@ERROR <> 0) GOTO ROLL
COMMIT TRAN

RETURN 0

ROLL:
ROLLBACK TRAN

RETURN 1
------------------------------------

After execution, the stored procedure failed but the modification made by statement-1 and statement-2 remained in the database but not statement-3, -4 and -5.

I did a debugging session and found the execution failed on statement-3 and the processing was terminated right there (i.e. it never reached the IF (@@ERROR<>0)statement right after it). Here is the error message (between two ------ lines):

------
Server: Msg 547, Level 16, State 1, Procedure abc, Line 103
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_ABC'. The conflict occurred in database 'DBABC', table 'XYZ', column 'XYZ_NUMBER'.


The statement has been terminated.

@RETURN_VALUE = N/A
------

I thought a transaction is used to guarantee all or none of the data modification takes effect. Why it is not working here? How can we achieve the goal of transaction processing in this case?

Your input and assistance is much appreciated.

9295

 

[hmckillop]

Have a look at

set xact_abort

in books online.

"I'm living so far beyond my income that we may almost be said to be living apart