I am running into a problem where a part of one of my triggers is failing. This failure is causing the transaction that wraps the trigger to rollback. The problem comes in that it is not raising an error to the invoke point of the command. It is acting like there was no error within the transaction and the only way it is caught is that the data that should have been changed isn't. In my example what happens is there are many changes in the transaction. The error occurs in the trigger. everything is rolled back, however, the caller of the command doesn't see a SQL Exception. The caller is never notified that there was an issue.
Is there any way to instruct TSQL to throw the exception in a way that it will report an error to the invoke point of the command?
CREATE TABLE Archive (
aColumn INT NOT NULL
)
CREATE TABLE Source (
aColumn INT NULL
)
--
CREATE TRIGGER Archive_Trigger ON Source
AFTER UPDATE
AS
BEGIN
INSERT INTO Archive
SELECT DELETED.aColumn
FROM DELETED
END
-- Other attempts
CREATE TRIGGER Archive_Trigger ON Source
AFTER UPDATE
AS
BEGIN
BEGIN TRY
INSERT INTO Archive
SELECT DELETED.aColumn
FROM DELETED
END TRY
BEGIN CATCH
SET XACT_ABORT ON;
DECLARE @ErrorMessage NVARCHAR(4000) ;
SET @ErrorMessage = ERROR_MESSAGE() ;
RAISERROR('Error %s occurred in Archive Trigger', 16, 1, @ErrorMessage) ;
END CATCH
END
This is a really naive version of what the trigger is doing. The problem isn't happening on a production system. When it is happening is in a development environment. When the /Source/ table changes the /Archive/ table must also change. From time to time the archive is forgotten and that is when the error occurs. For example if aColumn was not null in archive and null in Source. This instance would cause the problem.
After having a row in the table with a null aColumn if I execute the following code. The result I expect is to get an exception. I don't.
DataSource source;
Connection connection = source.getConnection();
PreparedStatement statement = connection.prepareStatement("UPDATE Source SET aColumn = NULL");
statement.executeUpdate();
The RAISERROR statement is not a severe error that halts processing. In fact, MSDN says:
The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.
You can verify this behavior by running the following SQL:
set xact_abort on
begin tran
raiserror('error 1', 16, 1, null)
raiserror('error 2', 16, 1, null)
The output will be:
Msg 50000, Level 16, State 1, Line 3
error 1
Msg 50000, Level 16, State 1, Line 4
error 2
Any attempt to raise a severe error will fail unless you are a sysadmin:
Msg 2754, Level 16, State 1, Line 8 Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
I do think you should use SET XACT_ABORT ON, but either:
1) Do a SET XACT_ABORT ON during the actual INSERT. This will cause the original error to terminate the connection.
For example:
CREATE TRIGGER Archive_Trigger ON Source
AFTER UPDATE
AS
BEGIN
SET XACT_ABORT ON
INSERT INTO Archive
SELECT DELETED.aColumn
FROM DELETED
END
2) Alternatively, you could cause a more severe error in the BEGIN CATCH statement. For example, cause a divide by 0 error with a SELECT 1/0. The client will have to ignore the divide by zero error to get the error message you raised.
Note that the original SET XACT_ABORT setting will be restored after leaving the trigger (search for "trigger").
When the trigger does cause an error that cancels the trigger, SQL Server will raise the error 3609 to the client with the text:
The transaction ended in the trigger. The batch has been aborted.
We had this specific issue happen in our application. A transaction was killed inside a trigger. The client application did not receive the error and continued onward outside of a transaction. Very bad things happened at this point.
We solved this by having our data access object (DAO) detect when the current database connection is closed, broken, or the transaction is null. In this case, if a SQL error was returned in a SqlException.Errors with a number of 3609, the DAO throws a specific "the transaction ended in the trigger" exception.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments