Transactions - Expert Oracle Database Architecture - page 274

In this situation, it is less clear what should happen. If the error occurs after the trigger has fired, should the
effects of the trigger persist, or not? That is, if the trigger fired and updated T2 , but the row was not inserted into T ,
what should the outcome be? Clearly the answer is that we don't want the CNT column in T2 to be incremented if a row
is not actually inserted into T . Fortunately in Oracle, the original statement from the client— INSERT INTO T , in this
case—either entirely succeeds or entirely fails. This statement is atomic. We can confirm this, as follows:

EODA@ORA12CR1> set serveroutput on
EODA@ORA12CR1> insert into t values (1);
I fired and updated 1 rows

1 row created.

EODA@ORA12CR1> insert into t values(-1);
I fired and updated 1 rows
insert into t values(-1)
*
ERROR at line 1:
ORA-02290: check constraint (EODA.SYS_C0061484) violated

EODA@ORA12CR1> select * from t2;

CNT
----------
1

■ When using sQL*plus from oracle9 i release 2 and before, in order to see that the trigger fired, you need to
add a line of code, EXEC NULL , after the second INSERT . this is because sQL*plus does not retrieve and display the
DBMS_OUTPUT information after a failed DML statement in those releases. in oracle 10 g and above it does.

Note

So, one row was successfully inserted into T and we duly received the message I fired and updated 1 rows .
The next INSERT statement violates the integrity constraint we have on T . The DBMS_OUTPUT message appeared—the
trigger on T in fact did fire and we have evidence of that. The trigger performed its updates of T2 successfully. We
might expect T2 to have a value of 2 now, but we see it has a value of 1. Oracle made the original INSERT atomic—the
original INSERT INTO T is the statement, and any side effects of that original INSERT INTO T are considered part of
that statement.
Oracle achieves this statement-level atomicity by silently wrapping a SAVEPOINT around each of our calls to the
database. The preceding two INSERT s were really treated like this:

Savepoint statement1;
Insert into t values ( 1 );
If error then rollback to statement1;
Savepoint statement2;
Insert into t values ( -1 );
If error then rollback to statement2;