triggers statement level atomicity

Question

What does Oracle mean by "statement level atomicity"?

Answer 1

It means that any single SQL statement you run is atomic in nature - it will either succeed completely or fail completely. If your SQL statement fails, and triggers that would have run as a result of that SQL statement will fail as well.

Answer 2

Let's cite a couple of chapters from the Concepts:

Statement-Level Read Consistency

Oracle always enforces statement-level read consistency. This guarantees that all the data returned by a single query comes from a single point in time--the time that the query began. Therefore, a query never sees dirty data nor any of the changes made by transactions that commit during query execution. As query execution proceeds, only data committed before the query began is visible to the query. The query does not see changes committed after statement execution begins.

Statement-Level Rollback

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been run. This operation is a statement-level rollback.

A SQL statement that fails causes the loss only of any work it would have performed itself. It does not cause the loss of any work that preceded it in the current transaction.

Answer 3

This is a statement about the nature of transactions: a Unit Of Work either succeeds or completes in its entirety. If your transaction comprises two inserts plus an update, and the update statement fails, the database will rollback all three statements. Any recursive SQL or other SQL such as that included in triggers is included in the atomic scope of the transaction. Find out more.

Atomicity means the A in the ACID principles of transaction management.