Can you use a commit statement within a database trigger?

Answer 1

Yes, you can very definitely use transactions within a trigger in one of two common ways;

1. As with any other use of transactions - to ensure a collection of commands within the trigger are executed in a repeatable way in the event of failure.

2. To 'undo' the process that caused the trigger to fire. For example, the trigger might be doing some additional integrity checks and issuing a rollback to undo the initiating update/delete.

---------

AnswerCome to think of it, I have never seen a commit on triggers. Triggers as you know are a last line defense based on an action that was carried out by an insert,update or delete. Hence, since the trigger exists on the server, and is only fired if one of the above conditions are executed then it seems pointless to use a commit in the trigger. Also commit goes along nicely with transactions where you begin the transaction and if all is well then you issue a commit, else if the transaction fails, then you issue a rollback.