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;

  3 Responses to “Buy one, get one free auditing (or The problem with non-transactional triggers) SQL”

  1. there’s another much simpler problem with non-transactional triggers: the “firing” transaction can simply be rolled back !


    sokrates > drop trigger i_can_execute_twice;

    Trigger dropped.

    sokrates > alter table when_you_were_expecting_once rename column so_susceptible_to_dml_restarts to gets_wrong_on_rollback;

    Table altered.

    sokrates > create trigger can_you_spell_rollback
    2 before update or delete on when_you_were_expecting_once
    3 for each row
    4 declare
    5 pragma autonomous_transaction;
    6 begin
    7
    8 insert into so_this_simple_audit_history (
    9 may_have_more_records,
    10 than_you_were_expecting
    11 ) values (
    12 :old.as_this_is_non_transactional,
    13 :old.gets_wrong_on_rollback
    14 );
    15
    16 commit;
    17
    18 end can_you_spell_rollback;
    19 /

    Trigger created.

    sokrates > select count(*) from so_this_simple_audit_history;

    COUNT(*)
    ----------
    0

    sokrates > delete when_you_were_expecting_once;

    1 row deleted.

    sokrates > rollback;

    Rollback complete.

    sokrates > select count(*) from so_this_simple_audit_history;

    COUNT(*)
    ----------
    1

    • 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!

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)