Trigger and Store Procedure and Transaction.

Question

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.