Statement restart

Today I explained the “statement restart” problem to a colleague.
Every database developer has to know about this one…

Short: Any statement can be restarted!
This means that when you do one update statement, oracle CAN restart the statement.
Every code in a trigger can be executed multiple times, so watch out with package variables and autonomous transactions in triggers.

Some code to test it:

CREATE TABLE test_trigger(val NUMBER)
/

CREATE OR REPLACE PACKAGE global_var
IS
g_val NUMBER;
END global_var;
/

CREATE OR REPLACE TRIGGER test_trig
BEFORE UPDATE
ON TEST_TRIGGER
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
global_var.g_val := global_var.g_val + 1;
END ;
/

BEGIN
global_var.g_val := 0;
END;
/

INSERT
INTO test_trigger
( val
)
VALUES
( 0
)
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

BEGIN
FOR i IN 1..100000
LOOP
UPDATE test_trigger
SET val = val +1;
END LOOP;
END;
/

DECLARE
l_val NUMBER;
BEGIN
SELECT val
INTO l_val
FROM test_trigger;

dbms_output.put_line(‘value in package global variable:’||global_var.g_val);
dbms_output.put_line(‘value in table:’||l_val);
END;
/

What’s the value of your global variable?

Or like Tom Kyte says: “Triggers or evil!”
More on this topic by Tom Kyte: That old restart problem again