create
PACKAGE                      cfr4521.pacepack as
 
operation_name      varchar2(30) := null;
procedure set_operation_name ( oper_name varchar2 );
function get_operation_name return varchar2;
 
end pacepack;
 
create 
PACKAGE BODY        cfr4521.PACEPACK as
 
  FUNCTION get_operation_name return varchar2 is
    begin
      return nvl(pacepack.operation_name,' ');
    end;
 
  PROCEDURE set_operation_name ( oper_name varchar2 ) as
 
  begin
     pacepack.operation_name := oper_name;
  end set_operation_name;
 
end pacepack;

create table cfr4521.delete_data
as
select owner, object_name
from dba_objects
where rownum < 53001;

create or replace trigger cfr4521.delete_data_bt
before delete on cfr4521.delete_data
for each row
begin
 
 if nvl(cfr4521.pacepack.get_operation_name,'NADA') != 'NO UP' then
   raise_application_error(-20500, 'Not NO UP!');
 end if;
 cfr4521.pacepack.set_operation_name(null);
 
exception when others then
 cfr4521.pacepack.set_operation_name(null);
 raise;
end;

declare
 
CURSOR L_DELETE_CSR IS
  SELECT * FROM cfr4521.delete_data                                                  
  for update;      
 
BEGIN
 
 for l_delete_row in l_delete_csr
 loop
 
  cfr4521.pacepack.set_operation_name('NO UP');
  DELETE FROM cfr4521.delete_data
      where current of L_delete_CSR;                                     
 
 end loop;
 
END;

... Effectively, our business rules are coded into our table triggers ...

frequent readers of this site can already see where this is going:

https://www.oracle.com/technetwork/issue-archive/2008/08-sep/o58asktom-101055.html

triggers - ugh, you just have to hate them. Especially when someone says "our business rules are coded into them". cringe....


... We have a global variable which we use to short-circuit these business rules from executing. ....

and now I really cringe :( Magic on Magic...

Now, before we discuss this - you must be made aware that not only do we NOT promise to fire the trigger once - we actually promise to fire it more than once in certain cases:

http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html
http://asktom.oracle.com/Misc/part-ii-seeing-restart.html
http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

this has *always* been true - complex logic in triggers is a scary scary thing (I don't are what database you are using - it is scary).


You are doing something non-transactional in a trigger - we are doing a statement level rollback silently on you (we do those, we have always done those, we will always do those). When we rollback - your code doesn't "rollback".

This is why doing anything non-transactional in a trigger is a really really bad idea - modifying globals in a trigger - you have to be really really really careful there.

Here is a simplified example, you'll see similar results in 9i, 10g, 11g - that is all I tested on:

ops$tkyte%ORA11GR2> drop table delete_data;

Table dropped.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace PACKAGE pacepack
  2  as
  3      type array is table of number index by varchar2(40);
  4      g_data array;
  5      g_cnt  number;
  6  end pacepack;
  7  /

Package created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table delete_data
  2  as
  3  select owner, object_name
  4  from dba_objects
  5  where rownum < 53001;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace trigger delete_data_bt
  2  before delete or update on delete_data
  3  for each row
  4  begin
  5      if ( pacepack.g_data.exists(rowidtochar(:old.rowid)) )
  6      then
  7          dbms_output.put_line( 'doing "' || :old.rowid || '" again was called ' || pacepack.g_cnt );
  8      else
  9          pacepack.g_data(rowidtochar(:old.rowid)) := 1;
 10      end if;
 11      pacepack.g_cnt := pacepack.g_cnt + 1;
 12  end;
 13  /

Trigger created.

ops$tkyte%ORA11GR2> declare
  2      CURSOR L_DELETE_CSR IS
  3      SELECT *
  4        FROM delete_data
  5         for update;
  6      l_cnt number := 0;
  7  BEGIN
  8       pacepack.g_data.delete;
  9       pacepack.g_cnt := 0;
 10      for l_delete_row in l_delete_csr
 11      loop
 12            update delete_data
 13             set owner = lower(owner)
 14           where current of L_delete_CSR;
 15          l_cnt := l_cnt + 1;
 16      end loop;
 17      dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt || ' local count = ' || l_cnt );
 18  END;
 19  /
doing "AAAYMZAAEAABFk7AAQ" again was called 24634
doing "AAAYMZAAEAABFm2ABd" again was called 35899
doing "AAAYMZAAEAABFnxABh" again was called 47164
trigger count = 53003 local count = 53000

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> declare
  2      CURSOR L_DELETE_CSR IS
  3      SELECT *
  4        FROM delete_data
  5         for update;
  6      l_cnt number := 0;
  7  BEGIN
  8       pacepack.g_data.delete;
  9       pacepack.g_cnt := 0;
 10      for l_delete_row in l_delete_csr
 11      loop
 12            DELETE FROM delete_data
 13           where current of L_delete_CSR;
 14          l_cnt := l_cnt + 1;
 15      end loop;
 16      dbms_output.put_line( 'trigger count = ' || pacepack.g_cnt || ' local count = ' || l_cnt );
 17  END;
 18  /
doing "AAAYMZAAEAABFfSACs" again was called 3878
doing "AAAYMZAAEAABFf0ACR" again was called 11795
doing "AAAYMZAAEAABFkcABZ" again was called 19235
doing "AAAYMZAAEAABFmHAAE" again was called 26641
doing "AAAYMZAAEAABFmtAAM" again was called 34092
doing "AAAYMZAAEAABFnTAC8" again was called 41464
doing "AAAYMZAAEAABFn6ACQ" again was called 48844
trigger count = 53007 local count = 53000

PL/SQL procedure successfully completed.

You cannot do things in a trigger that cannot be rolled back - unless you are willing to have them NOT BE rolled back.