How to handle errors raised by triggers

May 23, 2011 at 2:57 PM

How can I handle errors that are raised (RAISERROR ('this is an error', 16, 1)) within a trigger?
When I execute a simple SQLTest with TST, that the only thing it does is an INSERT into a table with a trigger, it returns the following error message:

"
Error: The transaction is in an uncommitable state after the test procedure '[Expedientissimo].[dbo].[sqltest_dummy]' has failed. A rollback was forced. The TEARDOWN if any will be executed outside of a transaction scope.
"

The code:
create table dbo.dummy(
 id int identity primary key
 , name varchar(50) not null
)
GO
create trigger dbo.dummy_i on dbo.dummy
for insert
as
 raiserror('TRIGGER_ERROR', 16, 1)
GO
create procedure dbo.sqltest_dummy
as
 exec TST.Assert.RegisterExpectedError 'Test 1', 'TRIGGER_ERROR';
 insert into dbo.dummy values('a')
GO
exec TST.Runner.RunTest 'Expedientissimo', 'sqltest_dummy'

Thanks in advance,
Mário Araújo

May 23, 2011 at 4:39 PM

Use TST.Assert.RegisterExpectedError in your test before you run the code that will generate the error.

 

I use the RegisterExpectedError to validate my error handling routines work as expected.

May 23, 2011 at 4:50 PM

It was what I've done. Can you test the code I posted before?

May 23, 2011 at 5:02 PM

I tried it and got the same message you did. Since the RAISERROR in the trigger is causing a ROLLBACK, I think you need to disable the transaction handling the TST is doing. Try adding this to your test database and see if that fixes things for you:

 

CREATE PROCEDURE TSTConfig
AS
BEGIN
   ---------------------------------------------------------------------------------------------------------------------------------
   EXECUTE TST.Utils.SetConfiguration
           @ParameterName='UseTSTRollback'
          ,@ParameterValue='0'
          ,@Scope='Test'
          ,@ScopeValue='sqltest_dummy';
   ---------------------------------------------------------------------------------------------------------------------------------
END

 

Once I added that, the test passed.

May 23, 2011 at 5:13 PM

Well, the fix works for me too... But if the test failed because, for some reason, the error is not raised, my database will stay dirty.

May 23, 2011 at 5:50 PM

If the expected error isn't raised, the test will fail.

 

What exactly are you trying to test?

May 23, 2011 at 10:43 PM

Yes, the test will fail but the INSERT data will persist in the database.

I want to run some tests in my production database and at the end I need to guarantee that the database hasn't changed.

May 23, 2011 at 11:43 PM

You shouldn't be running tests against a production database.

 

One option would be to backup the production system, restore it elsewhere and run the tests on the copy.

Coordinator
May 27, 2011 at 8:46 AM

John is right, one should not run TST against a production database.
However mfa is also right because even when run in a test environment his expectation for this scenario to work and the test database to remain clean (as in unaffected by the test) is a legitimate one.

I consider this a bug in the TST framework. Here is some more context: A RAISERROR inside a trigger will invalidate the state of a transaction. That is a problem but only when the test has an associated TEARDOWN. In that case the TEARDOWN would be forced to run outside of the transaction that TST uses to wrap the tests in. A TEARDOWN that runs outside such transaction is an undesired scenario and TST would signal that as an error. However, if there is no TEARDOWN and the error that the trigger raises is expected, TST should be able to handle this situation.

I am going to fix this bug in the coming weekend or maybe the next one and release a new version.

Coordinator
May 28, 2011 at 9:38 AM

Mário,

The issue is now fixed in the new release: V1.7.

May 30, 2011 at 10:26 AM

Thanks Ladi for your help.