The rollback transaction request has no corresponding begin transactio 1

[sujosh]

Even though I have a Begin and End around the Rollback transaction why do I get this error? Is this anything do with this option "XACT_ABORT" ? Below is my code. Could you please tell where I am going wrong?


if not exists (select * from inserted where subs1 is null)

Begin
RAISERROR ('Field SUBS1 Is Read Only. Updating this field will cause loss of integrity', 16, 1)
Begin
ROLLBACK TRANSACTION;
End
End

 

[vongrunt]

Use BEGIN TRAN or BEGIN TRANSACTION. BEGIN/END only group logically statements like {} do in some other languages (C, Java, blah).

 

[sujosh]

I tried your suggestion and I keep getting

When changed it to Begin transaction and End transaction
Incorrect syntax near the keyword 'Transaction'.

When changed it to Begin tran and End tran
Incorrect syntax near the keyword 'Tran'.

Let em know if I need to change something else

if not exists (select * from inserted where subs1 is null)

Begin
RAISERROR ('Field SUBS1 Is Read Only. Updating this field will cause loss of integrity', 16, 1)
Begin Transaction
ROLLBACK TRANSACTION
End Transaction
End

Thanks

 

[ESquared]

You can only rollback things that have happened in between a pair of BEGIN TRANSACTION and COMMIT or ROLLBACK TRANSACTION statements.

DECLARE
    @Key int,
    @Err int

BEGIN TRANSACTION

--Let's say MyTable has a list of names with an identity/autonumber field. We will insert a new name. Assume the following insert is successful:

INSERT INTO MyTable VALUES('George')

--Get the value of the identity column for the just-inserted record (Don't use @@IDENTITY!!!!!!)
SELECT @Key = SCOPE_IDENTITY

--But let's say the following insert breaks some constraint or validation rule, returning an error (and not inserting)
INSERT INTO MyRelatedTable (NewNameID) VALUES (@Key)

SELECT @Err = @@Error
IF @Err>0
  ROLLBACK TRANSACTION
ELSE
  COMMIT TRANSACTION

You cannot rollback any operation that is not made inside of a transaction.

 

[sujosh]

Got it!! Thanks a lot. You are very helpful

 

[SQLSister]

Good explanation Esquared.