Transactions - Oracle Database Transactions and Locking Revealed - page 78

•

SET TRANSACTION : This statement allows you to set various transaction attributes, such as
the transaction's isolation level and whether it is read-only or read-write. You can also use
this statement to instruct the transaction to use a specific undo segment when using manual
undo management, but this is not recommended. We'll discuss manual and automatic undo
management in more detail in Chapter 8.

That's it—there are no other transaction control statements. The most frequently used control statements are
COMMIT and ROLLBACK . The SAVEPOINT statement has a somewhat special purpose. Internally, Oracle uses it frequently;
in fact Oracle uses it every time you execute any SQL or PL/SQL statement, and you may find some use for it in your
applications as well.

Atomicity
Now we're ready to see what's meant by statement, procedure, and transaction atomicity.

Statement-Level Atomicity
Consider the following statement:

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:

EODA@ORA12CR1> create table t2 ( cnt int );
Table created.

EODA@ORA12CR1> insert into t2 values ( 0 );
1 row created.

EODA@ORA12CR1> commit;
Commit complete.

EODA@ORA12CR1> create table t ( x int check ( x>0 ) );
Table created.

EODA@ORA12CR1> create trigger t_trigger
2 before insert or delete on t for each row
3 begin
4 if ( inserting ) then
5 update t2 set cnt = cnt +1;
6 else
7 update t2 set cnt = cnt -1;
8 end if;
9 dbms_output.put_line( 'I fired and updated ' ||
10 sql%rowcount || ' rows' );
11 end;
12 /
Trigger created.