In message <1121981274.327834_at_yasure>, DA Morgan <damorgan_at_psoug.org>
writes
>Saeed wrote:
>> 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
>
>Since you don't mention a version I'll guess 10g and suggest you use
>Fine Grained Auditing ... DBMS_FGA.
It's actually a 8.1.7.4 database - for a variety of complex reasons
(mainly organisational) an upgrade to 9i or later will not be performed
until early next year. Had we been on 9i then Oracle Streams looks a
promising avenue, but as it is we have to make an 8i solution work.
Received on Mon Jul 25 2005 - 07:22:09 CDT
