Hi,

I am trying to setup a trigger on table A to read data from table B and save to table C.
The insert is done on both table A and B on the same session/commit.
It seem that the trigger on table A does not see the data that is committed on table B at that time.

Here is a sample test case;


create table t1 (
pk number,
t1c1 number,
t1c2 number
);

create table t2 (
pk number,
t2c1 number,
t2c2 number
);

create table t3 (
t3c1 number,
t3c2 number
);


create or replace
TRIGGER T_TEST_T1
after insert or update on t1
for each row
begin
insert into t3 (t3c1,t3c2) values ( :new.t1c1, :new.t1c2) ;
insert into t3 (t3c1,t3c2) ( select t2c1,t2c2 from t2 where pk = :new.pk) ;
exception
WHEN OTHERS THEN
null;
end;
/

insert into t1(pk,t1c1,t1c2) values (0,1,2);
insert into t2(pk,t2c1,t2c2) values (0,10,20);
commit;

select * from t3;

drop trigger t_test_t1;
drop table t1;
drop table t2;
drop table t3;
At this point table 3 should have 2 rows, but it only has 1 as the trigger does not see the row that was inserted in t2 so
select t2c1,t2c2 from t2 where pk = :new.pk
does not exist yet when the trigger is fired.
T3C1 T3C2
---------- ----------
1 2
and I should see
T3C1 T3C2
---------- ----------
1 2
10 20
Is there any way to get this to work?