Buy one, get one free auditing (or The problem with non-transactional triggers) SQL

create table when_you_were_expecting_once (as_this_is_non_transactional integer, so_susceptible_to_dml_restarts integer);
create table so_this_simple_audit_history (may_have_more_records integer, than_you_were_expecting integer);
create trigger i_can_execute_twice
before update or delete on when_you_were_expecting_once
for each row
declare
pragma autonomous_transaction;
begin
insert into so_this_simple_audit_history (
may_have_more_records,
than_you_were_expecting
) values (
:old.as_this_is_non_transactional,
:old.so_susceptible_to_dml_restarts
);
commit;
end i_can_execute_twice;
/
show errors
insert into when_you_were_expecting_once values (1, 1);
commit;
-- run the following in two sessions at the same time without committing
-- (the second session will be blocked)
update when_you_were_expecting_once
set so_susceptible_to_dml_restarts = so_susceptible_to_dml_restarts + 1;
-- then commit them
-- you would expect there to be two rows in here (because you ran two updates) right?
select * from so_this_simple_audit_history;
-- wrong!
-- there's three because Oracle had to "restart" the second update
-- because you're trying to get a read consistent view of columns that have changed
drop table when_you_were_expecting_once purge;
drop table so_this_simple_audit_history purge;

Good point Matthias. The rollback is probably more of an issue if you’re doing something like sending an email – getting two confirmations is a bit weird but not really an issue; getting one for something that hasn’t happened could be a big problem!