Trigger Problem

Question

Post reply

Trigger Problem

pbd_76

SSC Rookie

Points: 45
More actions
April 6, 2002 at 12:42 am

#158279

Hi All,
I want to use Begin Transaction and Commit Transaction in my trigger. But when I used it and want to RollBack the Transaction following error has occured.
Server: Msg 6401, Level 16, State 1, Procedure itrg_FO_Package, Line 37
Cannot roll back tr. No transaction or savepoint of that name was found.
The statement has been terminated.
I have defined my trasaction as "tr" but why I can't I rollback only the transaction between Begin transaction and Commit transaction.
Can anybody help me.
Thanks in advance.
Pradip

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Can you post what you have done including the process that started the transaction?

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

pbd_76 SSC Rookie Points: 45 More actions · Answer 2

Following is code I used :-

IF (boolen condition)

BEGIN

BEGIN TRANSACTION tr

WHILE (@ld_FromDate <= @ld_ToDate)

BEGIN

SELECT @li_WeekDay = DATEPART(dw, @ld_FromDate)

IF SUBSTRING(@lv_ValidOn, @li_WeekDay, 1) = 'Y'

BEGIN

INSERT INTO Statement

IF @@ERROR <> 0

BEGIN

RAISERROR('Error', 16, 1);

ROLLBACK TRANSACTION tr;

RETURN;

END

SELECT @ld_FromDate = DATEADD(d, 1, @ld_FromDate)

END

COMMIT TRANSACTION tr

END

This code gives me an error

Server: Msg 6401, Level 16, State 1, Procedure itrg_FO_Package, Line 37

Cannot roll back tr. No transaction or savepoint of that name was found.

The statement has been terminated

But if I removed Begin trans and commit trans and used only rollback it works fine.

What the problem?

Thnaks

Antares686 SSC Guru Points: 125444 More actions · Answer 3

Sorry you did not set your savepoint properly.

You did

BEGIN TRANSACTION tr

needs to be

SAVE TRANSACTION tr

to set transaction savepoint within a transaction. Remember a trigger event occurrs within the same transaction as the statement that invoked it, which by not setting a savepoint can roll the whole back (not just the trigger). But a SAVE savepoint will allow you to roll the trigger itself back.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

pbd_76 SSC Rookie Points: 45 More actions · Answer 4

Thanks a lot James !!!

I got my error .....

Pradip