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 ?

Estivation

Did you try to add a commit? If you did you’d see an error - it isn’t allowed (unless it’s autonomous). – Regenerative

I didn't add a commit. Wanted to confirm the behavior before that. – Estivation

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.

Swampy

Answer 2

Any change that a trigger does is committed with the transaction that fired the trigger.

So yes, the change done inside the trigger will be committed "automatically".

You can't commit inside a trigger anyway.

Sallyanne

Answer 3

Trigger should not commit and cannot commit. Committing in a trigger usually raises an exception unless it happens into autonomous transaction.

When you open connection, Oracle creates session for it. When you start to modify data (insert into TableA) Oracle starts transaction. Trigger on TableA works into same session & transaction; insert made into TableB belongs to this transaction. When you commits, it commits both inserts.

Craniate