Tuesday, October 11, 2016

Oracle trigger use default statement level read consistency

Test case included
=============================

SQL> conn donghua/XXXXXX
Connected.
SQL> create table t1 (i integer, d timestamp);
Table created.

SQL> create table t2 (i integer, d timestamp);
Table created.

SQL> create table t2_history (t2_i integer, t2_d timestamp,d timestamp);
Table created.

SQL> create or replace trigger t1_trig after insert on t1
  2  begin
  3  dbms_lock.sleep(10);
  4  insert into t2_history select i, d, systimestamp from t2;
  5  dbms_lock.sleep(10);
  6  insert into t2_history select i, d, systimestamp from t2;
  7* end;
SQL> /
Trigger created.

===============> Session 1 ======================

SQL> insert into t1 values (1,systimestamp);
1 row created.

===============> Session 2 ======================

SQL> insert into t2 values (1,systimestamp);
1 row created.

SQL> commit;
Commit complete.

<-- --="" few="" seconds="" wait="">

SQL> insert into t2 values (2,systimestamp);
1 row created.

SQL> commit;
Commit complete.

===============> Session 1 ======================
SQL> commit;
Commit complete.

SQL> select * from t1;

         I D
---------- ----------------------------
         1 11-OCT-16 04.20.32.288000 AM

SQL> select * from t2;

         I D
---------- ----------------------------
         1 11-OCT-16 04.20.36.648000 AM
         2 11-OCT-16 04.20.43.991000 AM

SQL> select * from t2_history;

      T2_I T2_D                         D
---------- ---------------------------- ----------------------------
         1 11-OCT-16 04.20.36.648000 AM 11-OCT-16 04.20.42.304000 AM  <-- 1st="" fired="" font="" in="" insert="" into="" t2_history="" trigger="">
         1 11-OCT-16 04.20.36.648000 AM 11-OCT-16 04.20.52.789000 AM  <-- 2nd="" fired="" font="" in="" insert="" into="" t2_history="" trigger="">
         2 11-OCT-16 04.20.43.991000 AM 11-OCT-16 04.20.52.789000 AM  <-- 2nd="" fired="" font="" in="" insert="" into="" t2_history="" trigger="">