Skip to Main Content

Triggers on Materialized View Fast Refresh

842886Sep 6 2011 — edited Sep 6 2011
Hello all!

I have a Materialized View defined as below:
CREATE MATERIALIZED VIEW mxsvativi
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
WITH PRIMARY KEY
AS
SELECT codativ, TRIM (ramo) AS ramo, percdesc FROM tbativi;
And, on this MV, I have a trigger declared as:
CREATE OR REPLACE TRIGGER TK_SYNC_FV_MXSATIVI
BEFORE DELETE OR INSERT OR UPDATE
OF CODATIV
  ,RAMO
  ,PERCDESC
ON MXSVATIVI 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
   voper     NUMBER;                                --Operation
BEGIN
   IF INSERTING
   THEN
      voper := 0;
   ELSIF UPDATING
   THEN
      voper := 1;
   ELSIF DELETING
   THEN
      voper := 2;
   END IF;
 
   --Save voper to a tblOperLog
 
END;
This trigger should save some data on a table called tblOperLog, based on the operation that is being performed on the MV (Insert, Delete or Update).

I´ve tested this on Oracle 10G and also on Oracle 11G (11.1). On these, when we perform a Update on the parent table (on this eg, TBATIVI) the trigger TK_SYNC_FV_MXSATIVI records an Update Operation on the table tblOperLog. But, on a Oracle 11G 11.2.0.2.0 is logging a Delete and a Insert for each update that is done.

Does anybody know how could I force the proccess that updates the MV to emits an update instead of a delete/insert?

Thanks a lot!
Comments
Post Details
Added on Sep 6 2011
4 comments
6,258 views