BEFORE Trigger with restarting statements.

BEFORE Triggers Fired Multiple Times

If an UPDATE or DELETE statement detects a conflict with a concurrent UPDATE, then Oracle Database performs a transparent ROLLBACK to SAVEPOINT and restarts the update. This can occur many times before the statement completes successfully. Each time the statement is restarted, the BEFORE statement trigger is fired again. The rollback to savepoint does not undo changes to any package variables referenced in the trigger. Your package should include a counter variable to detect this situation.

SQL> drop table employees;

Table dropped.

SQL> drop package pck_employees;

Package dropped.

SQL> create table employees(
  2     employee_id   integer,
  3     last_name     varchar2(100),
  4     salary        number);

Table created.

SQL> insert into employees
  2  select rownum, 'emp'||rownum, substr(dbms_random.value, 2, 5) as salary
  3    from dual connect by level <= 100000;

100000 rows created.
SQL> commit;

Commit complete.

SQL> alter table employees add primary key (employee_id);

Table altered.

SQL> exec dbms_stats.gather_table_stats(user, 'employees', cascade=>true);

PL/SQL procedure successfully completed.

SQL> create or replace package pck_employees is
  2     g_count  number;
  3     g_lastid number;
  4  end pck_employees;
  5  /

Package created.

SQL> create or replace trigger trg_employee_changes
  2  before update on employees
  3     for each row
  4  begin
  5
  6     if pck_employees.g_lastid = :new.employee_id then
  7         dbms_output.put_line('Restarted:'||pck_employees.g_lastid);
  8     end if;
  9
 10     pck_employees.g_count := pck_employees.g_count + 1;
 11     pck_employees.g_lastid := :new.employee_id;
 12
 13  end trg_employee_changes;
 14  /

Trigger created.

SQL> set serveroutput on
SQL> declare
  2     c_iterations constant pls_integer := 100000;
  3  begin
  4
  5     pck_employees.g_count := 0;
  6
  7     for x in 1 .. 2 loop
  8
  9        for i in 1 .. c_iterations loop
 10           update employees
 11              set salary = salary + 1
 12            where employee_id = i;
 13        end loop;
 14
 15        dbms_output.put_line(pck_employees.g_count);
 16
 17     end loop;
 18
 19     commit;
 20
 21  end;
 22  /
100000
Restarted:7714
Restarted:18851
Restarted:29988
Restarted:41124
Restarted:52261
Restarted:63397
Restarted:74536
Restarted:85675
200008

PL/SQL procedure successfully completed.

http://www.plsqlchallenge.com/pls/apex/f?p=10000:651:3441462995850933::NO::P651_QUIZ_ID:2030
http://tkyte.blogspot.com/2010/04/that-old-restart-problem-again.html