Database Reference
In-Depth Information
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 on), 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.
Procedure-Level Atomicity
It is interesting to note that Oracle considers PL/SQL blocks to be statements as well. Consider the following stored
procedure and reset of the example tables:
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 insert into t values ( 1 );
5 insert into t values (-1 );
6 end;
7 /
Procedure created.
EODA@ORA12CR1> delete from t;
0 rows deleted.
EODA@ORA12CR1> update t2 set cnt = 0;
1 row updated.
EODA@ORA12CR1> commit;
Commit complete.
EODA@ORA12CR1> select * from t;
no rows selected
EODA@ORA12CR1> select * from t2;
CNT
----------
0
So, we have a procedure we know will fail, and the second INSERT will always fail in this case. Let's see what
happens if we run that stored procedure:
EODA@ORA12CR1> begin
2 p;
3 end;
4 /
I fired and updated 1 rows
I fired and updated 1 rows
 
Search WWH ::




Custom Search