Trigger and Store Procedure and Transaction.

elham deljooei

13y1.7k

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

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

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

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.

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Riyaz Akhtar13y ago
Hi Elham,

Check below links:

triggers:

http://www.codeproject.com/Articles/25600/Triggers-Sql-Server

Stored Procedure

http://www.codeproject.com/Articles/126898/Sql-Server-How-to-write-a-Stored-procedure-in-Sql

Views:

http://www.w3schools.com/sql/sql_view.asp
0
elham deljooei13y ago
Hi,
Thanks for your answer.
I don't distinguish, When i have to use Trigger in my project.
I'm confused, When I have to use Store Procedure or Trigger or View?
Please help me with an example. Thanks a million friends.

Regards,
Elham
0
Riyaz Akhtar13y ago
Actually you don't have to use trigger for your requirement.

you are doing rollback in your trigger if more than 1 record is deleted.

if you see above code it will delete the record if record is only one, hence you don't have to rollback.

hope it answered your query..
0
elham deljooei13y ago
I can write it in my Store Procedure, I don't know when i exactly have to use Trigger?
Could you tell me a little more?

Thanks,
Elham
0
Iftikar Hussain13y ago
you can use trigger in a situation like you need send a mail whenever there is an updation on your table etc.

Regards,
Iftikar
0
Riyaz Akhtar13y ago
According to ur requirement you should not use either transaction nor trigger.

you want to delete if u have only one record, then check the count before deleting,

see below code:

ALTER Procedure [dbo].[Clerk_Delete]
@clk_Idint
As
Begin
IF((SELECT COUNT(1) FROM Clerk WHERE clk_Id = @clk_Id )=1)
BEGIN
Delete Clerk Where clk_Id = @clk_Id
END
End
0
elham deljooei13y ago
Could you tell me When I have to use trigger?

Thanks,
Elham
0
Iftikar Hussain13y ago
In that case don't create trigger. It is not good to use. Same thing you put in your SP.
use @@ROWCOUNT to get number of rows affected

ALTER Procedure [dbo].[Clerk_Delete]
@clk_Id int
As
Begin
Begin Try
declare @rowcount int
Begin Tran a
Delete from Clerk Where clk_Id = @clk_Id
select @rowcount=@@ROWCOUNT
if @rowcount>1
BEGIN
Rollback Tran a;
END
ELSE
BEGIN
Commit Tran a;
END

End Try
Begin Catch
Rollback Tran a;
End Catch
End

Regards,
Iftikar
0
elham deljooei13y ago
Hi Iftikar,
First Thanks for your answer.
I want to say If I want to remove 2 or more clk_Id from Clerk table at the moment, rollback all act.
I meant to say don't allow to remove more than one clk_Id at the momunt.

Regards,
Elham
0
Iftikar Hussain13y ago
Hi,
Can you please explain why you have declared DELETE trigger with TRANSACTION?

Regards,
Iftikar
0