October 20, 2011 at 9:56 pm
Hi,
How do I add a rollback tran after checking a condition inside a trigger.
Thanks.
October 21, 2011 at 12:49 am
you can have transaction inside a trigger,
October 21, 2011 at 7:08 am
So what is the error?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
October 24, 2011 at 10:09 pm
Hi,
The trigger is fired when an update happens in a table. After I check a particular condition and when its true I need to rollback the transaction.
The problem is whenever it comes across a rollback tran inside a trigger I get a runtime error and my application does not work and it freezes.
Thanks
October 25, 2011 at 8:11 am
Post the trigger code so we can take a look.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
November 2, 2011 at 4:39 pm
Trigger code is
ALTER
TRIGGER [dbo].[trgNameSel_Upd] on [dbo].[Name_Selection] for UPDATE
AS
Begin
if @IsMixedCntry_i = 1
begin
raiserror('Rollback tran',16,1)
rollback
set @msg = 'Rollback: SET REG - Specialties of Mixed Countries selected: ' + @PrefVals_i
if len(@msg) > 230 set @msg = left(@msg,230) + ' etc'
Begin
insert into name_log (Date_Time, LOG_TYPE, SUB_TYPE, USER_ID, ID, LOG_TEXT)
select CURRENT_TIMESTAMP,'CHANGE','ROLLBACK', @user, @ID_i, @msg
return
End
end
November 2, 2011 at 4:55 pm
OK. This is interesting. You start a transaction somewhere else, update some data, and then check for an error condition inside a trigger. If the error condition is met, you want to roll back the transaction from inside the trigger.
You could do something like this
WHILE @@TRANCOUNT > 1
ROLLBACK;
I believe that would work. I feel that the dependency between the rest of your code and this trigger would be a little hard to see, though. I would question the wisdom of implementing the logic in this way, just because it makes your code harder to maintain.
It's your choice, though! 🙂
November 2, 2011 at 7:36 pm
Hi, I assume (as you have not told us) that the problem is that when you roll back the transaction, you get an error
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
The way to stop this error is to begin a new transaction.
Put something like this in your trigger in place of the Rollback - it will ensure that the trigger exits with the same number of active transaction levels as it started with...
DECLARE @tc INT;
SET @tc = @@TRANCOUNT;
ROLLBACK TRAN;
WHILE @tc>0
BEGIN
BEGIN TRAN;
SET @tc = @tc - 1;
END;
Of course, you must still provide feedback to the procedure that was updating the table such that it can handle the fact that you have rolled back the transaction, by using a RAISERROR and/or your log table.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply