FOR EACH ROW trigger fires *at least* once per row

I have a table with a 'BEFORE UPDATE FOR EACH ROW' trigger.
The table has just one row and I update that row a thousand times. How many times does the trigger fire ?

It depends...though I'm not sure on what. This was the basis for one of Steven Feuerstein's PL/SQL Challenge questions. Here's a demonstration.


DROP TABLE plch_employees purge;
CREATE TABLE plch_employees
(  employee_id   INTEGER
 , salary        NUMBER)
/


INSERT INTO plch_employees VALUES (100, 0);


CREATE OR REPLACE PACKAGE plch_emp_count
IS
   g_count NUMBER;
END plch_emp_count;
/


CREATE OR REPLACE TRIGGER plch_employee_changes
   BEFORE UPDATE
   ON plch_employees
   FOR EACH ROW
BEGIN
   plch_emp_count.g_count := plch_emp_count.g_count + 1;
END;
/


CREATE OR REPLACE FUNCTION f_trg_test 
    (i_iterations IN NUMBER default 1000000) 
RETURN VARCHAR2 IS
  v_ret varchar2(100);
  v_cnt number := 1;
BEGIN
  plch_emp_count.g_count := 0;
  WHILE v_cnt < i_iterations LOOP
    UPDATE plch_employees
    SET salary = salary + 1
    WHERE employee_id = 100;
    IF plch_emp_count.g_count > v_cnt THEN
      v_ret := 'MisMatch at :'||v_cnt;
      v_cnt := i_iterations;
    END IF;
    v_cnt := v_cnt + 1;
  END LOOP;
  v_ret := NVL(v_ret, 'Reached '||plch_emp_count.g_count);
  commit;
  return v_ret;
END;
/
set serveroutput on
exec dbms_output.put_line(f_trg_test);

When I run it on my XE instance, I get a mismatch, generally after 718 rows. I get another, smaller, peak at 11518. These indicate a restart which is discussed in a couple of Tom Kyte posts listed here.

In summary a restart is where the session has found a row that needs update, fires the row trigger, then tries to update the row...and doesn't. It then retries the update, finding the row again and succeeds. 

Apparently there is some discussion about 'single-session' restarts being related to UNDO. 

I'll leave the reasons open. But it is worth remembering that with a BEFORE UPDATE trigger, FOR EACH ROW means it will fire at least once for each row, not exactly once.