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?