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.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Thu Jul 21 2005 - 16:27:50 CDT
