June 1, 2011 at 5:24 am
Hi,
I'm having hassle with transactions that are nested in both stored procedures and triggers.
I'm working on a SQL2005 database that has business validation implemented via triggers. These triggers are of a standard format (which cannot be changed) that means that entering invalid data cause a rollback in the trigger. These data changes have historically been made either with dynamic SQL from a front end or directly to the database via Management Studio, hence the business rules in triggers.
I'm now trying to implement some data changes via stored procedures that have nested transactions but whenever the validation triggers execute their rollbacks my procedure exits with "Msg 3902, Level 16, State 1, Procedure proc_Test, Line 19 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."
Here's a mock up of the triggers and my proc:
create table Test(Id int not null identity(1, 1) primary key, Things varchar(50) null)
go
create trigger tr_Test on Test for insert, update as
-- Do some validation here.
-- Purposefully fail the validation to demonstrate the problem.
rollback
raiserror('Trigger failed.', 16, -1)
return
go
create procedure proc_Test as
declare @errorCond bit
set @errorCond = 0
begin tran
begin try
-- do some stuff that will also need rolling back if the insert into Test fails
insert into Test values('kadsjdnf')
end try
begin catch
set @errorCond = 1
end catch
-- Either commit or rollback depending on whether or not we have an error flagged
if @errorCond = 0
begin
if @@trancount > 0
commit
end
else
begin
if @@trancount > 0
rollback
end
-- As it'll always fail, just return a 1
return 1
go
This all works fine but at some stage in the future I will want to reuse this procedure alongside other similar procedures inside another procedure and with another transaction. For example, like this:
declare @res int
begin tran
-- Do some stuff here that will also need rolling back if proc_Test fails.
exec @res = proc_Test
if @res = 0
begin
if @@trancount > 0
commit
end
else
begin
if @@trancount > 0
rollback
end
Things now start to go wrong. If the trigger executes a rollback, running the above will result in the following error:
Msg 266, Level 16, State 2, Procedure proc_Test, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.
My question is this; how can I avoid getting this error? I understand that the rollback inside the trigger resets @@trancount back to 0 and that the begin tran before the procedure call means that @@trancount is 1 when the proc is started, and that this is causing this error, but as far as I can see, the transactions and data are all managed sufficiently for this not to be an issue, so why do I have to suffer the error? The stored proc needs to be able to stand on its own so needs its own transaction but there is the likelihood that it will be reused too. Any help on what I can do to do prevent this would be really really deeply appreciated! Cheers.
June 1, 2011 at 4:21 pm
In my opinion you're asking for trouble using rollback in a trigger for this very reason. If you want to go there then you'll have to use SAVE TRANSACTION which is awkward and tough to maintain.
You mentioned that the proc "needs to be able to stand on its own"...well let it. What are you trying to accomplish by having ROLLBACK in the trigger?
If you want to validate data submitted to the table in a trigger, and not perform the data changes if the data does not live up to your expectations then use an INSTEAD OF trigger. This way you can do the validation before SQL Server attempts to write it to the table, and then if it fails your validation you can raise an error/return from the trigger.
This brings up a side question for me as well...what are you validating in the trigger that cannot be done through the use of a proper data type on your columns, column or table CHECK CONSTRAINTs, DRI or a combination?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2011 at 5:06 pm
Thanks for the reply opc.three. Unfortunately I can't change the format of the triggers. Rightly or wrongly, the database itself is open to users and they can pretty much just type insert and update statements into management studio if they like, in fact quite a few do. This is why there are these triggers which perform rollbacks. It was like that before I joined the company and, for various reasons that are beyond the scope of this forum, it's likely to be like that for the life of the database, which is also likely to be a while yet.
In answer to your side question, the database uses check constraints and DRI too but, because of the open nature of the database, the triggers still add a whole heap of further necessary validation. And it's the open nature of the database that means the stored proc needs to manage its own transaction whilst also being able to stand in a nested transaction too. (In fact, I will probably add some logic to the proc so that it does not start its own transaction if there's one it can join, but that's neither here nor there because the rollback in the trigger will cause the same issue.)
June 1, 2011 at 6:05 pm
I have to agree with opc.three that rollbacks in triggers are not to be encouraged, however you are stuck with this apparently....so
Those triggers do need to be changed, but you don't have to remove the rollback (unless I become Boss of your company 😀 )...
CREATE TRIGGER SampleTrigger ON SampleTranTest AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @opentran INT;
SELECT @opentran = @@TRANCOUNT;
IF 1=1 -- some real error condition
BEGIN
-- an error is to be raised but we want to rollback the transaction
-- this is really dangerous because the trigger has no idea what else is in the transaction!!!
ROLLBACK TRAN;
-- you then need to recreate the opening condition to avoid errors
WHILE @opentran>@@TRANCOUNT
BEGIN TRAN;
-- and throw your custom error
RAISERROR('Business Logic Failure',16,1);
END;
This will prevent error messages about transaction count imbalances, and leave it safe for your stored proc to still commit/rollback - although if the trigger has "done it's bad stuff" your stored proc is not "handling" it's own transaction any more anyway....
Of course, the real world is difficult, but you don't have to contribute to that - you could always take this opportunity to say to your bosses that you cannot write good code for them while those people are allowed to perform ad-hoc updates and those triggers are in place....:cool:
Best wishes!
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);June 1, 2011 at 6:27 pm
Yes, you're right there Mr Magoo, the real world is certainly a difficult place and one where I've learned the hard way that there's more to life than writing good code (:crying:), although it is still towards the top of my list. Anyway, I'm in danger of getting off topic here but you can be sure that changes are afoot. But until then...
Cheers, for the idea of replacing the transaction count. I might not get away with changing the triggers (there's rather a lot of them) but I can add it to my proc.
Cheers,
Tim
June 1, 2011 at 9:30 pm
mister.magoo (6/1/2011)
CREATE TRIGGER SampleTrigger ON SampleTranTest AFTER INSERT
AS
SET NOCOUNT ON;
DECLARE @opentran INT;
SELECT @opentran = @@TRANCOUNT;
IF 1=1 -- some real error condition
BEGIN
-- an error is to be raised but we want to rollback the transaction
-- this is really dangerous because the trigger has no idea what else is in the transaction!!!
ROLLBACK TRAN;
-- you then need to recreate the opening condition to avoid errors
WHILE @opentran>@@TRANCOUNT
BEGIN TRAN;
-- and throw your custom error
RAISERROR('Business Logic Failure',16,1);
END;
I have never seen something like that out in the wild...or in captivity for that matter. Very creative solution MM, you've got the goods again 😉
GuitarGuy, any thought on converting your AFTER triggers to INSTEAD OF triggers as previously mentioned? Maybe that's what you meant by not being able to change the "format of the triggers" but I figured it was worth another check-in because I think the swap may suit your requirements (at least what I have heard of them so far). With INSTEAD OF triggers there is no need to issue a ROLLBACK at all, yet they will still allow for doing all the necessary validation currently in place. This will keep your ad hoc users from touching data they are not supposed to touch, while empowering your procs to control the transactions they begin.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 1, 2011 at 10:02 pm
Yup, hats off to Mr Magoo, his idea works nicely, although I added the code to my proc rather than any triggers.
Nope, can't change the triggers, sorry. To start with there's nearly 1000 of them and some of them are pretty complex. Actually, once you get used to the way it's setup it doesn't work as badly as might imagine, current problem notwithstanding. The database suffers from more than its fair share of technical debt but its users love it. Nobody here is under any illusions about its shortcomings but these things take time.
Thanks to both of you for all your help.
Cheers,
Tim
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply