Using TSQLUnit to test INSTEAD OF triggers

Question

I have an INSTEAD OF trigger on a table in my SQL Server 2005 database that checks several incoming values. If an incoming value is invalid, an error is raised and the transaction is rolled back. Otherwise the record is inserted.

I would like to include a TSQLUnit test of this trigger where, if an invalid value is inserted, having the transaction rolled back is the successful outcome of the test. I have created a test procedure to do this, but rolling back the transaction aborts execution of the whole suite of tests.

Has anyone had success with this? If so, how did you accomplish it?

If this is not possible with TSQLUnit, how do you test your triggers? Or do you test them at all?

Answer 1

I don't know if it's TSQLUnit or just standard behavior of SQL you're seeing.

A trigger exception in SQL Server 2000 and earlier is batch aborting on ROLLBACK because @@TRANCOUNT = 0

With TRY/CATCH in SQL Server 2005 the behavior changes and the client should handle it correctly. Saying that, I'd wrap the outer call in TRY/CATCH too anyway.

Suggestions:

• check the state of data before and after to see what you have
• use a stored procedure (which is what you're doing anyway)
• use TRY/CATCH