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
CREATE OR REPLACE TRIGGER plch_employee_changes
FOR EACH ROW
plch_emp_count.g_count := plch_emp_count.g_count + 1;
CREATE OR REPLACE FUNCTION f_trg_test
(i_iterations IN NUMBER default 1000000)
RETURN VARCHAR2 IS
v_cnt number := 1;
plch_emp_count.g_count := 0;
WHILE v_cnt < i_iterations LOOP
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;
v_cnt := v_cnt + 1;
v_ret := NVL(v_ret, 'Reached '||plch_emp_count.g_count);
set serveroutput on
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.