If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

Per Row trigger to access data from other tables in same session..

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.

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.