August 13, 2014 at 3:40 pm
Hi there,
I'm hoping someone can help us. We have a trigger that fires off a stored procedure using dynamic SQL. It is getting this generic error message and I haven't been able to find the true cause of the error. The odd thing is that I can run the dynamic SQL without the trigger and it works just fine. The other issue is that I'd like it to end gracefully and not throw an error if it doesn't work. Any thought?
This is the error message.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
Here is the trigger code:
ALTER TRIGGER SAADownload_trigger ON StateCompletion
FOR UPDATE, Insert
AS
SET NOCOUNT ON
IF ( UPDATE(Finalized))
BEGIN
declare @updSubgrantdetailID int,
@finalizedbit,
@spnamevarchar(50),
@spname2varchar(50),
@SQLTextvarchar(200),
@procExistsint
Select @finalized = Finalized from inserted
IF @finalized = 1
BEGIN
select @updSubgrantdetailID = subgrantdetailID from inserted
set @spname = 'bjareports.dbo.sp_FGN' + cast(@updSubGrantDetailID as varchar(10)) + 'Download'
set @spname2 = 'sp_FGN' + cast(@updSubGrantDetailID as varchar(10)) + 'Download'
Select @procExists = count(*)
from bjareports.sys.procedures
where NAME = @spname2
If @procExists > 0
BEGIN
BEGIN TRY
set @SqlText = 'Exec ' + @spname
EXEC(@SQLText)
END TRY
BEGIN CATCH
IF XACT_STATE() = 1
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
print ERROR_MESSAGE()
END
END CATCH
END
END
END
August 13, 2014 at 7:03 pm
Before we get to your problem, be advised that your trigger is written to handle only one row even if the StateCompletion table is made to suffer an update on more than one row at a time.
Also, there's no BEGIN TRANSACTION in the trigger and the only way you enter the CATCH block is if there's an error. Therefor, there's no need to COMMIT anything. Remove the COMMIT block from the CATCH block because you're trying to commit an un-commit-able transaction.
The only time the ROLLBACK in the CATCH block should be executed (remember... you only enter the CATCH block if there's an error) is when IF XACT_STATE() = 1 or -1 or <> 0 (the safest method IMHO).
--Jeff Moden
August 14, 2014 at 8:32 am
Ooops! Thank you, Jeff. I just added that catch/try and I forgot the begin tran. But even when I add it, I'm still getting the error. I feel like the trigger is somehow hiding the real error message with the generic one. I want to find what is causing the error and also we could like the error to not abend the ongoing process after the trigger completes. I just want to find a way to gracefully exist.
August 14, 2014 at 10:49 pm
So take the commit out of the Catch Block. If you really want to use an explicit transaction in the trigger code, do the commit at the end of the Try Block.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply