February 18, 2013 at 11:01 am
I have a table with a UNIQUE Constraint on it. I have lots of different bits of code that insert records into this table. When I first implemented all of this code, I did not implement any error handling. (Why would I need error handling? Everything was working! :hehe:)
Suddenly, however, I got a constraint violation error which caused my application to crash, and I suddenly got the bright idea that I should capture and log these errors with TRY CATCH blocks. However, I did not want to update 40 different blocks of code that insert records to this table. So, then I got the really bright idea that I would put the TRY CATCH block into an INSTEAD OF trigger. That way I could capture any constraint violations on the table in one place.
However, when I try that, I get the following error:
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Could some bright spark out there explain why I get this error message? I can't find the flaw in my logic. Are triggers not intended to be used in this way? More than anything, I want to understand why this is not working. Thank you for your help! 🙂
Here is the code to create my primary table (the one the application tries to write to):
CREATE TABLE ExceptionInstanceTemp
( ExceptionID INT NOT NULL
CONSTRAINT [FKInstancesTemp]
FOREIGN KEY( ExceptionID )
REFERENCES ExceptionIndex( ExceptionID )
,ComparisonID BIGINT NULL
,InstanceText VARCHAR(894) NOT NULL
,InstanceDate DATETIME NOT NULL
DEFAULT ( GETDATE() )
,Outcome SMALLINT NOT NULL
DEFAULT ( 0 )
,CONSTRAINT [UniqueComparisonTemp]
UNIQUE NONCLUSTERED
(ExceptionID
,ComparisonID
)
);
Here is the code for the table to which I want to log errors writing to the main table:
CREATE TABLE ExceptionErrorLog
(ErrorDate SMALLDATETIME NULL
,ErrorNumber INT NULL
,ErrorMessage VARCHAR(1000) NULL
);
Here is the trigger I have created on the first table:
CREATE TRIGGER dbo.tr_ExceptionInstanceTemp_INSERT
ON dbo.ExceptionInstanceTemp
INSTEAD OF INSERT
AS
BEGIN /*TRIGGER*/
BEGIN TRY
INSERT Exception.dbo.ExceptionInstanceTemp
( ExceptionID
,ComparisonID
,InstanceText
)
SELECT ExceptionID
,ComparisonID
,InstanceText
FROM Inserted;
END TRY
BEGIN CATCH
INSERT Exception.dbo.ExceptionErrorLog
( ErrorDate
,ErrorNumber
,ErrorMessage
)
SELECT GETDATE() AS ErrorDate
,ERROR_NUMBER() AS ErrorNumber
,REPLACE( ERROR_MESSAGE()
,'Violation of UNIQUE KEY constraint ''UniqueComparisonTemp''. Cannot insert duplicate key in object ''dbo.ExceptionInstanceTemp''. The '
,''
) AS ErrorMessage;
END CATCH /*Outer CATCH*/
END /*TRIGGER*/
GO
Thank you, again. I greatly appreciate any insights you can provide. 😀
February 18, 2013 at 12:51 pm
Issue a ROLLBACK in the trigger after the CATCH and before the INSERT.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 18, 2013 at 1:59 pm
David Moutray (2/18/2013)
More than anything, I want to understand why this is not working.
All triggers are implicitly part of a transaction, with at least the SQL statement that caused the trigger to fire included in the transaction.
In implicit transaction mode, where each SQL statement is its own transaction, SQL must either commit or rollback the transaction at the end of (all) triggers.
By default, if you don't tell it otherwise, SQL will attempt a commit. (That makes sense: you sure wouldn't want it rolling back by default!)
However, in your case, you had a fundamental error in the INSERT, therefore SQL cannot commit it. But it tries, because it needs to end the transaction.
So, as the previous posted stated, you need to address that in your CATCH section. Previous poster didn't mention XACT_STATE(), but you do need to check that too, because you don't want to try to ROLLBACK if a trans is not active, because that will cause an error too:
...
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION
...rest of CATCH code here...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2013 at 4:06 pm
OK. My problem is that any constraint violation within a TRY CATCH block within a trigger makes the transaction uncommittable (whether explicit or implicit).
At that point, no matter what I do, an error is returned to the application. If I don't roll back the transaction, then the transaction is rolled back automatically at the end of the trigger and a 3630 error is returned (as in my initial attempt).
If I do roll back the transaction, then I get a different error:
The transaction ended in the trigger. The batch has been aborted.
For example, I tried the following code:
ALTER TRIGGER [dbo].[tr_ExceptionInstanceTemp_INSERT]
ON [dbo].[ExceptionInstanceTemp]
INSTEAD OF INSERT
AS
BEGIN /*TRIGGER*/
BEGIN TRY /*Outer TRY*/
BEGIN TRANSACTION AttemptInsert;
INSERT Exception.dbo.ExceptionInstanceTemp
( ExceptionID
,ComparisonID
,InstanceText
)
SELECT ExceptionID
,ComparisonID
,InstanceText
FROM Inserted;
SELECT XACT_STATE(), 'Before Rollback';
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION AttemptInsert;
SELECT XACT_STATE(), 'After Rollback';
END TRY /*Outer TRY*/
BEGIN CATCH /*Outer CATCH*/
SELECT XACT_STATE(), 'Outer Catch Block';
BEGIN TRY
IF XACT_STATE() <> 0
ROLLBACK TRANSACTION;
SELECT XACT_STATE(), 'Inner Try Block'
END TRY
BEGIN CATCH
SELECT XACT_STATE(),'Inner Catch Block'
END CATCH
END CATCH /*Outer CATCH*/
END /*TRIGGER*/
GO
Two records are returned and then the Msg 3609 error above.
1. -1 Outer Catch Block
2. 0 Inner Try Block
So, tracing the execution, here is what happened:
1. I attempted to do an insert in the outer try block which violated the UNIQUE Constraint. Execution immediately passed to the Outer Catch Block.
2. The XACT_STATE() was determined to be -1 (uncommittable).
3. The Inner Try Block is attempted.
4. The entire transaction is rolled back.
5. The XACT_STATE() was determined to be 0 (no active transaction)
Then the trigger ends, but an error is still thrown: "Msg 3609, The transaction ended in the trigger, The batch has been aborted."
There does not seem to be any way to capture that last error within the trigger. If that is correct, then my proposed solution won't work: using an INSTEAD OF trigger to capture and log the error and allow the stored procedure (or other application) to continue executing without the need for any error handling code of its own.
Is this correct? Is this how triggers are supposed to work?
February 18, 2013 at 4:53 pm
You don't want to issue your own BEGIN TRANSACTION in the trigger, since, as I noted before, you're already in a transaction.
That error message after the ROLLBACK is normal, to indicate that an unrecoverable occurred. Unfortunately, I don't think you can prevent that.
You could pre-check the data for validation errors and only do the INSERT if the data was clean, but that would force you to write code to validate the constraints rather than just SQL doing it, so that's not a good option either.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2013 at 4:55 pm
You are doing circular trigger firing:
CREATE TRIGGER ...
ON dbo.ExceptionInstanceTemp -- trigger fires on insert command
INSTEAD OF INSERT
AS
BEGIN
INSERT dbo.ExceptionInstanceTemp -- insert command fires trigger (again, and again, and again...)
...
You make insert command that fills INSERTED metatable and executes a trigger code.
Trigger makes insert command that fills INSERTED metatable and executes a trigger code.
Trigger makes insert command that fills INSERTED metatable and executes a trigger code.
...
I think you can't catch constraint failure with instead of trigger on the same table.
Probably the simplest way would be to put the common logic into a procedure and call that procedure from all 40 places you are inserting into that table.
February 18, 2013 at 5:07 pm
ScottPletcher (2/18/2013)
All triggers are implicitly part of a transaction, with at least the SQL statement that caused the trigger to fire included in the transaction.In implicit transaction mode, where each SQL statement is its own transaction, SQL must either commit or rollback the transaction at the end of (all) triggers.
OK, I think it is becoming a little clearer now, as I think about it. Whenever a transaction causes a trigger to fire, everything in that trigger becomes a part of that same transaction.
If you try to start a new explicit transaction in a trigger, that does not really start a new transaction. It just adds another nesting "level" to the existing transaction.
Operations inside a trigger have to be an atomic transaction: all or nothing. This necessary feature intersects with an aspect of TRY CATCH blocks to create this problem. As it says in Books Online (Using TRY...CATCH in Transact-SQL):
Uncommittable Transactions
Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.
The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1.
When using TRY CATCH blocks in triggers, if a severe enough error occurs to fire the CATCH block, then the whole transaction becomes uncommittable - which includes everything in the trigger. So, if you do anything which will cause a write to the database transaction log in that trigger and you don't completely roll it back, it will be automatically rolled back at the end of the trigger and an error thrown.
You can return records from a SELECT statement. I presume you could write records to a text file. You can't write anything to any database table.
Thank you for your help. This was driving me mildly insane. :crazy:
February 18, 2013 at 5:24 pm
David Moutray (2/18/2013)
ScottPletcher (2/18/2013)
All triggers are implicitly part of a transaction, with at least the SQL statement that caused the trigger to fire included in the transaction.In implicit transaction mode, where each SQL statement is its own transaction, SQL must either commit or rollback the transaction at the end of (all) triggers.
OK, I think it is becoming a little clearer now, as I think about it. Whenever a transaction causes a trigger to fire, everything in that trigger becomes a part of that same transaction.
Exactly!
If you try to start a new explicit transaction in a trigger, that does not really start a new transaction. It just adds another nesting "level" to the existing transaction.
Yep. And since SQL Server doesn't really support nested transactions, you're just "doodling in code".
Operations inside a trigger have to be an atomic transaction: all or nothing. This necessary feature intersects with an aspect of TRY CATCH blocks to create this problem. As it says in Books Online (Using TRY...CATCH in Transact-SQL):
Uncommittable Transactions
Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.
The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1.
When using TRY CATCH blocks in triggers, if a severe enough error occurs to fire the CATCH block, then the whole transaction becomes uncommittable - which includes everything in the trigger. So, if you do anything which will cause a write to the database transaction log in that trigger and you don't completely roll it back, it will be automatically rolled back at the end of the trigger and an error thrown.
You can return records from a SELECT statement. I presume you could write records to a text file. You can't write anything to any database table.
Thank you for your help. This was driving me mildly insane. :crazy:
Dead on ... except at the very end :-). You can do writes to tables and so after the uncommittable error occurs ... but first you must do a ROLLBACK. After the ROLLBACK, you can write to log tables, etc.. But, since the error did occur, ultimately SQL will always relay that back to the original statement -- I don't think we can do anything to prevent that (except perhaps for "zapping" the sysmessages table to lower the error level for validation errors, and I'm almost certain you don't want to go that far!).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 18, 2013 at 6:58 pm
XACT_STATE() is irrelevant here. Value of -1 means we are in uncommitable transaction and our only option is to rollback.
Since we are doing rollback anyway, checking XACT_STATE() is irrelevant. It would be relevant only if we want to try a commit.
Trigger wont fire itself forever as I thought, but only once - strange, but true (tested that).
Solution is to add these two lines right at the beginning of the catch block:
IF @@TRANCOUNT > 0 ROLLBACK -- end the transaction that trigger is in
BEGIN TRAN -- to avoid error after trigger finishes
We need to rollback to undo anything that is done in a transaction so far, as we do not want that to persist.
"Begin tran" after rollback is necessary because at the end of the trigger we normally should be in a transaction,
and because our rollback got us out of transaction, we have to start one.
Whole trigger, works as original poster expected:
alter TRIGGER dbo.tr_ExceptionInstanceTemp_INSERT
ON dbo.ExceptionInstanceTemp
INSTEAD OF INSERT
AS
BEGIN /*TRIGGER*/
BEGIN TRY
INSERT dbo.ExceptionInstanceTemp
( ExceptionID
,ComparisonID
,InstanceText
)
SELECT ExceptionID
,ComparisonID
,InstanceText
FROM Inserted;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK -- end the transaction that trigger is in
BEGIN TRAN -- to avoid error after trigger finishes
INSERT dbo.ExceptionErrorLog
( ErrorDate
,ErrorNumber
,ErrorMessage
)
SELECT GETDATE() AS ErrorDate
,ERROR_NUMBER() AS ErrorNumber
,REPLACE( ERROR_MESSAGE()
,'Violation of UNIQUE KEY constraint ''UniqueComparisonTemp''. Cannot insert duplicate key in object ''dbo.ExceptionInstanceTemp''. The '
,''
) AS ErrorMessage;
END CATCH /*Outer CATCH*/
END /*TRIGGER*/
GO
Cheers!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply