Using TSQLUnit to test INSTEAD OF triggers

I have an INSTEAD OF trigger on a table in my SQL Server 2005 database that checks several incomin开发者_JAVA百科g 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?

---

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

---

There is another TSQL unit testing framework called TST http://tst.codeplex.com/ that supposedly allows testing code that has its own transactions. From the TST docs:

"When the tested code has its own transactions, TST uses a reliable way of detecting the cases where its own rollback mechanism becomes ineffective. The TST rollback can be disabled at the test, suite or global level."

I have not used this framework, but I came across it (and your questions) while researching this topic.