I am getting the error "ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION". I am trying to rollback the transaction if the row count for any delete statement is zero. Given below is my code. What am I doing wrong? Please help
alter procedure delete_staff(@staffID varchar(10))
as
declare @tempvar varchar(50), @staffName varchar(50), @jobTitle varchar(50), @dept varchar(50)
begin transaction trans1
declare @rc1 int
declare @rc2 int
declare @rc3 int
select @tempvar = left(@staffID,1) from Staff
delete from staff where staffID = @staffID
set @rc1=@@rowcount
delete from Login where userID = @staffID
set @rc2=@@rowcount
begin
if(@tempvar='S')
begin
delete from Specialist where specialistID = @staffID
set @rc3=@@rowcount
end
else if(@tempvar='H')
begin
delete from Helpdesk_Operator where helpdesk_OperatorID = @staffID
set @rc3=@@rowcount
end
commit transaction trans1
end
if(@rc1=0 or @rc2=0 or @rc3=0)
begin
rollback transaction trans1
end
If you commit the transaction, you can't then make a rollback. Do one or the other:
if(@rc1=0 or @rc2=0 or @rc3=0)
begin
rollback transaction trans1
end else begin
commit transaction trans1
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With