Hi friends,
I have a procedure with transaction which remove clerk from clerk table and I've written a delete trigger for this table which it has transaction.
When I want to delete a clk_Id from clerk table, transactions have interference together.
Can you help me?
ALTER Procedure [dbo].[Clerk_Delete]
@clk_Idint
As
Begin
Begin Try
Begin Tran a
Delete Clerk
Where clk_Id = @clk_Id
Commit Tran a;
End Try
Begin Catch
Rollback Tran a;
End Catch
End
Alter TRIGGER Clerk_Delete1
ON Clerk
AFTER Delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
begin tran a
SET NOCOUNT ON;
Declare @count int;
Select @count = Count (*) From deleted
If @count >1
Begin
Rollback tran a;
End
else
Begin
Print 'ok';
End
END
If I want to remove 2 clk_Id at the moment,
like this procedure:
ALTER Procedure [dbo].[Clerk_Delete]
@clk_Idint,
@clk_Id1 int
As
Begin
Begin Try
Begin Tran a
Delete Clerk
Where clk_Id = @clk_Id or clk_Id = @clk_Id1
Commit Tran a;
End Try
Begin Catch
Rollback Tran a;
End Catch
End
I enforce with an error.
I don't know, What should i do?
The error is:
(0 row(s) affected)
Msg 3903, Level 16, State 1, Procedure Clerk_Delete, Line 15
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.