Restart phenomenon of flip-flop of UPDATE type

SQL_1> create table mytest(id int );

Table created.

SQL_1>insert into mytest select 1 from dual;

1 line created.

create or replace trigger tri_mytest_u before update on mytest for each row

begin

dbms_output.put_line('old_value: '||:OLD.id);

dbms_output.put_line('new_value: '||:NEW.id);

5 end;

6 /

Trigger created

SQL_1>show errors;

No mistakes.

SQL_1>update mytest set id=id+1;

old_value: 1

new_value: 2

1 line updated.

SQL> set serveroutput on size 20000

SQL> set sqlprompt SQL_1

In new window 2 perform:

-------------------------------

SQL> set serveroutput on size 20000

SQL> set sqlprompt SQL_2

SQL_2>update mytest set id=id+1;

---------------------------------

SQL_1>commit;

Submission complete.

Window 2 Execution Results:

-------------------------

SQL_2>update mytest set id=id+1;

old_value: 1

new_value: 2

old_value: 2

new_value: 3

1 line updated.

SQL_2>select * from mytest;

ID

----------

3

Conclusion: Update triggers restart to read committed transaction information if wait occurs while updating data.