Consider the following statement:
SQL> Insert into t values ( 1 );
It seems fairly clear that if the statement were to fail due to a constraint violation, the row would not be inserted. However, consider the following example, where an INSERT or DELETE on table T fires a trigger that adjusts the CNT column in table T2 appropriately:
$ sqlplus eoda/foo@PDB1
SQL> create table t2 ( cnt int );Table created.
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:
SQL> set serveroutput on
SQL> insert into t values (1);I fired and updated 1 rows1 row created.
SQL> insert into t values(-1);I fired and updated 1 rows
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 INSERTs 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.
In Oracle, this statement-level atomicity extends as deep as it needs to. In the preceding example, if the INSERT INTO T fires a trigger that updates another table, and that table has a trigger that deletes from another table (and so on and so forth), either all of the work succeeds or none of it does. You don’t need to code anything special to ensure this; it’s just the way it works.
