In this particular example, the trigger is for insert on an InvoiceItem table (e.g. it stores lines on an invoice). On insert, stock has to be allocated against each invoice line by inserting into another table. If all invoice lines cab be allocated then the invoice is successfull otherwise the whole transaction (inserting invoice and invoiceitems and allocating stock) needs to be rolled back.

Hope that makes sense.

My whole beef is that, I believe all data modelling can and should be achieved with primary keys, foreign keys and when they fail, triggers. These 3 tools and transaction handling should form the basis to the base data integrity of the database. I'm beginning to think that MS Sql Servers quirks with transaction and error handling makes this task so cumbersome it's almost impossible.

Where I'm at now is I've got everything working except for the pesky error that gets thrown when a stored procedure exits with a different trancount to when it entered. Is there some way of turning this error off?

Adam.