Per Row trigger to access data from other tables in same session..
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;
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
create table t1 (
create table t2 (
create table t3 (
create or replace
after insert or update on t1
for each row
insert into t3 (t3c1,t3c2) values ( :new.t1c1, :new.t1c2) ;
insert into t3 (t3c1,t3c2) ( select t2c1,t2c2 from t2 where pk = :new.pk) ;
WHEN OTHERS THEN
insert into t1(pk,t1c1,t1c2) values (0,1,2);
insert into t2(pk,t2c1,t2c2) values (0,10,20);
select * from t3;
drop trigger t_test_t1;
drop table t1;
drop table t2;
drop table t3;
select t2c1,t2c2 from t2 where pk = :new.pk
does not exist yet when the trigger is fired.
and I should see
Is there any way to get this to work?