It breaks transactional consistency.
A SQL statement should be an atomic unit. Either it processes all the data or none.
Allowing commit/rollback in triggers or functions called in SQL breaks this rule. An update of 100 rows could get part-way through, encounter an error, then undo its work. If you have a trigger committing as you go along, this is impossible.
my argument to myself was i still care to log & commit succeeded DMLs infoThe point is the DML
hasn't succeeded until it's updated
all 100 rows.
For example:
I create table of the number 1 .. 10. These must be greater than zero.
I then update them all to decrease their value by 1. So the row with value 1 will raise an exception:
create table t (
c1 check ( c1 > 0 )
) as
with rws as (
select level x from dual
connect by level <= 10
)
select * from rws
order by 1 desc;
create or replace trigger trg
before update on t
for each row
begin
dbms_output.put_line (
'Updating ' || :old.c1 || ' to ' || :new.c1
);
end;
/
update t
set c1 = c1 - 1;
Updating 10 to 9
Updating 9 to 8
Updating 8 to 7
Updating 7 to 6
Updating 6 to 5
Updating 5 to 4
Updating 4 to 3
Updating 3 to 2
Updating 2 to 1
Updating 1 to 0
ORA-02290: check constraint (CHRIS.SYS_C0016806) violated
If I could commit in the trigger the all the changes made before updating row 1 would be saved.
This breaks a fundamental principle of SQL.
Any writing you to do audit tables or similar is irrelevant. Allowing commits in the trigger breaks the "all or nothing" rule of SQL.
Of course, having a large UPDATE blow up because of one little error is annoying.
There are various tricks to get around this problem. The most widely applicable is the LOG ERRORS clause. This allows the database to record details of failing rows, while still completing the update "successfully".
Not convinced yet?
How about a more realistic example modelling bank transactions:
- There's a trigger on the transactions table
- When you insert a transaction, this updates the balances of the accounts involved
- If an account exceeds its credit limit, raise an error
I've used an autonomous transaction to allow committing of half a transaction. The trigger commits and raises an exception half-way through, leading to out-of-sync balances:
create table transactions (
from_acct int, to_acct int, amount number
);
create table accounts (
account_id int, balance number
);
insert into accounts values ( 1, 100 );
insert into accounts values ( 2, 100 );
commit;
select sum ( balance ) from accounts;
SUM(BALANCE)
200
create or replace trigger trg
before insert on transactions
for each row
declare
pragma autonomous_transaction;
begin
update accounts
set balance = balance + :new.amount
where account_id = :new.to_acct;
commit;
raise_application_error (
-20001,
:new.from_acct || ' exceeded credit limit'
);
update accounts
set balance = balance - :new.amount
where account_id = :new.from_acct;
end;
/
insert into transactions values ( 1, 2, 50 );
ORA-20001: 1 exceeded credit limit
select sum ( balance ) from accounts;
SUM(BALANCE)
250
Yikes!
Yes the example above is
terrible coding. But remember triggers could fire more triggers. Functions can call functions. Leading to all sorts of knock-on effects.
In the general case, allowing commits could lead to all sorts of nasty surprises!
So it's disallowed.