Why Cannot write commit in trigger?
3 Answers. Not only do triggers not need a COMMIT you can’t put one in: a trigger won’t compile if the body’s code includes a COMMIT (or a rollback). This is because triggers fire during a transaction. When the trigger fires the current transaction is still not complete.Click to see full answer. Similarly, you may…
3 Answers. Not only do triggers not need a COMMIT you can’t put one in: a trigger won’t compile if the body’s code includes a COMMIT (or a rollback). This is because triggers fire during a transaction. When the trigger fires the current transaction is still not complete.Click to see full answer. Similarly, you may ask, do we need commit in trigger?Yes ,You can Commit inside the trigger. But for this you have to make this trigger transaction to be a Independent transaction from its parent transaction, You can do this by using Pragma. Pragma AUTONOMOUS_TRANSACTION allow you to build the Indepadent(child) Transaction,started by another.Similarly, is commit necessary after insert? So yes, by default, if you’re just using INSERT , the records you insert will be committed, and there is no point trying to roll them back. (This is effectively the same as wrapping each statement between BEGIN and COMMIT .) Consequently, can we use commit or rollback in trigger? We can’t COMMIT/ROLLBACK in DML triggers because transaction is handled manually after DML statement. However, database triggers seems to be an exception. The trigger does not contain autonomous transaction procedure with commit inside that, so who is commiting the insert?Can we use Pragma Autonomous_transaction in trigger?FUNCTION balance (acct_id INTEGER) RETURN REAL IS PRAGMA AUTONOMOUS_TRANSACTION; my_bal REAL; BEGIN END; END banking; In the example below, you mark a database trigger as autonomous. Unlike regular triggers, autonomous triggers can contain transaction control statements.
