Transactions - Oracle Database Transactions and Locking Revealed - page 79

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;

For programmers used to Sybase or SQL Server, this may be confusing at first. In those databases exactly the
opposite is true . The triggers in those systems execute independently of the firing statement. If they encounter an
error, the triggers must explicitly roll back their own work and then raise another error to roll back the triggering
statement. Otherwise, the work done by a trigger could persist even if the triggering statement, or some other part of
the statement, ultimately fails.