How to abort a transaction?
-
Hi. Since triggers happen after action, the update is already there. A well behaved client would rollback transaction on exception from trigger, but ill behaved can ignore it and commit regardless. I think I saw somewhere that you can mark the transaction as dirty non-commitable, but can't find that topic again. So, how can a trigger block the update/insert/delete?
-
Try putting you trigger code in a TRY block and adding a CATCH block containing the ROLLBACK statement.
-
Thanks, but I'm aware of that. According to "best practices", a trigger should not mess with transactions.
Besides, a well behaved client gets kicked: starts transaction, executes update, trigger rolls back and raises exception, client tries to rollback and gets "no transaction active".
-
You could use INSTEAD OF triggers which occur before the action rather than after. Having said that I'm not sure that there is a "best practice" that says a trigger shouldn't mess with transactions. If you're going to use triggers then you're going to have to accept that you will have to manage transactions within them. Sure, you need to be aware of the potential pit falls.
As much as possible, I would leave SQL Server to managing your transactions (that's what it's designed for) rather than allowing the client to manage the transactions. Imagine this, if the client is responsible for starting and commiting transactions what were to happen if the client starts a transaction but then loses its connection to the SQL Server? The transaction remains opened for longer than you'd want (or worse still, indefinitely) and the client isn't around to rollback or commit - leaving resources locked and processes blocked.
That's my take on things anyway,
-
That does it. I completely missed the fact that instead of trigger happens before action. There are some restriction on this type of triggers, but better than nothing. Thank you.
Transactions should be managed by client, as client executes updates and client knows which statements belong to a batch (ie master-detail). If the client does not manage transactions, every statement is by default committed, which is OK for most uses. If a client loses connection or leaves transaction active for too long, server executes rollback.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply