Does an insert trigger need a commit statement

Question

This is an simplification of actual scenario; where is see missing records on Table B.

Say there are two db tables A ; B.

There is an on insert trigger on Table A;which do an insert to Table B (but it doesn't have COMMIT;). If we open a db connection through JDBC connector; and do an insert on Table A; and commit it; What is the behavior of Trigger? Will it be automatically committed the insert statement on table B ?

Answer 1

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. As COMMIT terminates a transaction allowing them in triggers would break the unit of work.

So changes executed in a trigger are committed (or rolled back) by the owning transaction which issued the DML that fired the trigger.

---

It is true that triggers can run under the PRAGMA AUTONOMOUS_TRANSACTION, in which case they must have a COMMIT. But this is an edge case, as there are few meaningful uses of nested transactions in Oracle.