Triggers on read only snapshots

Consider the following:

Database ORA1, table TAB1.

A materialized view log is created on TAB1.

Database ORA2, materialized view (read only snapshot) MV_TAB1 is created, base table TAB1_at_ORA1.

Now, we want to record the number of updates to a particular type of row. On DB1 we can put an UPDATE trigger on TAB1 which fires to maintain a count on some other table when some condition is met.

Suppose, we want to move this tallying trigger to the ORA2 database and remove it from ORA1. We recreate the trigger on ORA2 against MV_TAB1.

What I have found is that UPDATE triggers on Materialized Views are not firing. The reason for this is probably because, as explained in some MetaLink note the id for which I can't recall, when a MV Log records an update, and a fast fresh is performed, the snapshot row is deleted and re-created.

This also poses a risk for INSERT triggers, in that they could potentially fire each time a refresh is done if the source row was updated, obviously a problem if you are assuming that a row is only inserted once and therefore expect the INSERT trigger to fire just once in the lifetime of that row.

My question is have others come across this, and if so how have you got round it?

As ever in your responses, please try and stick to the scenario described, and don't go off on tangents about the wisdom of doing this that and the other - it's the particular problem/situation I have described that I interested in.

Saeed

ng_786 Received on Thu Jul 21 2005 - 11:36:12 CDT