Triggers...

It's an old story... first time I saw this behavior roughly 10 years ago, in 1997. Last century, last millennium. It was my first project abroad, in Denmark. I love Denmark. The project was about migrating some popular publishing software from Sybase to Oracle. That was old 8.0.3 release of Oracle.

Once I mentioned about this fact here [the thread is in Russian].

Below is an example of how Oracle trigger's behavior one just has to know about. Hope you find it useful. I'm using 10.2.0.3, it works same way on 11.1.0.7.0 too.

SQL> DEFINE rows = 100000

SQL> CREATE TABLE x (
  2    p                                NUMBER
  3  )
  4  /

SQL> CREATE OR REPLACE TRIGGER x$trg$bd
  2    BEFORE DELETE ON x
  3  BEGIN
  4    dbms_session.set_identifier('0');
  5  END;
  6  /

SQL> CREATE OR REPLACE TRIGGER x$trg
  2    BEFORE DELETE ON x
  3    FOR EACH ROW
  4  BEGIN
  5    dbms_session.set_identifier(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER') + 1);
  6  END;
  7  /

SQL> TRUNCATE TABLE x;

SQL> INSERT INTO x(p) SELECT ROWNUM FROM dual CONNECT BY LEVEL <= &&rows;
100000 rows created.

SQL> COMMIT;

SQL> VAR tx VARCHAR2(20)
SQL> EXEC :tx := dbms_transaction.local_transaction_id(TRUE);

SQL> COLUMN extends NEW_VALUE extends NOPRINT
SQL> SELECT r.extends
  2    FROM v$rollstat r
  3       , v$transaction t
  4       , v$session s
  5   WHERE s.sid = SYS_CONTEXT('USERENV', 'SID')
  6     AND t.addr = s.taddr
  7     AND r.usn = t.xidusn
  8  /

SQL> EXEC DELETE x;

SQL> 1 SELECT r.extends - &&extends extra
SQL> /

     EXTRA
----------
        12

SQL> COLUMN planned FORMAT 999999
SQL> COLUMN total FORMAT 999999
SQL> SELECT TO_NUMBER(&&rows) planned
  2       , TO_NUMBER(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER')) total
  3    FROM dual
  4  /

PLANNED   TOTAL
------- -------
 100000  100012


Life is cruel! Beware!

P.S.: BTW, now I definitely know why I love Denmark and a reason is explained here by Mogens. And he definitely knows all those reasons! :-)