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.
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;
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.
It seems to me that table "C" should be a view that queries data from tables "A" and "B", rather than storing duplicate data in a separate place. If you have the tables properly setup, it should not cost that much to query from table "A" and "B". It should be much cheaper that what you are proposing.
Why don't you use a materialized view. They can be setup to do incremental updates. Doing row level triggers to update flat reporting tables might cause a lot of overhead. If you create log tables under each of the 5 tables, you should be able to then create a materialized view with incremental update.
That seem to show that the "commit" has nothing to do with my trigger at this point. Also that any operation done before the trigger should be available, but the operation after, even before the commit, are not.