在MV上建立触发器实验

-- 建立MV测试表  

CREATE TABLE tbl1  

(  

   a   NUMBER,  

   b   VARCHAR2 (20)  

);  

  

CREATE UNIQUE INDEX tbl1_pk ON tbl1 (a);  

  

ALTER TABLE tbl1 ADD (CONSTRAINT tbl1_pl PRIMARY KEY(a));  

  

-- 建立MV日志，单一表聚合视图的快速刷新需要指定including new values子句  

CREATE MATERIALIZED VIEW LOG ON tbl1 INCLUDING NEW VALUES;  

  

-- 建立MV  

CREATE MATERIALIZED VIEW mv_tbl1  

BUILD IMMEDIATE  

REFRESH FAST  

START WITH TO_DATE('2013-06-01 08:00:00','yyyy-mm-dd hh24:mi:ss')  

NEXT SYSDATE + 1/24  

AS  

SELECT * FROM tbl1;  

  

-- 建立trigger测试表  

CREATE TABLE mv_tbl1_tri  

(  

   a   NUMBER,  

   b   VARCHAR (20),  

   c   VARCHAR (20)  

);  

  

-- 建立trigger  

CREATE OR REPLACE TRIGGER tri_mv  

   AFTER DELETE OR INSERT OR UPDATE  

   ON mv_tbl1  

   REFERENCING NEW AS new OLD AS old  

   FOR EACH ROW  

BEGIN  

   CASE  

      WHEN INSERTING THEN  

         INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'insert');  

      WHEN UPDATING THEN  

         INSERT INTO mv_tbl1_tri VALUES (:new.a, :new.b, 'update');  

      WHEN DELETING THEN  

         INSERT INTO mv_tbl1_tri VALUES (:old.a, :old.b, 'delete');  

   END CASE;  

EXCEPTION  

   WHEN OTHERS THEN  

      RAISE;  

END tri_mv;  

/  

  

-- 新增MV测试表数据  

INSERT INTO tbl1 VALUES (1, '测试数据1');  

INSERT INTO tbl1 VALUES (2, '测试数据2');  

INSERT INTO tbl1 VALUES (3, '测试数据3');  

  

COMMIT;  

  

  

SELECT * FROM tbl1;  

SELECT * FROM mlog$_tbl1; -- 生成3条“I”MV日志记录  

  

-- 手工刷新MV  

EXEC dbms_mview.refresh('mv_tbl1');  -- 刷新MV，触发3条insert，清空MV日志  

SELECT * FROM mlog$_tbl1;  

  

-- 检查trigger测试表  

SELECT * FROM mv_tbl1_tri;  

  

  

-- 修改MV测试表数据  

UPDATE tbl1  

   SET a = 13  

 WHERE a = 3;  

  

UPDATE tbl1  

   SET a = 3  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET a = 13  

 WHERE a = 3;  

  

UPDATE tbl1  

   SET a = 3  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET a = 13  

 WHERE a = 3;  

  

UPDATE tbl1  

   SET a = 13  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET a = 13  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET b = '测试数据13'  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET b = '测试数据3'  

 WHERE a = 13;  

  

UPDATE tbl1  

   SET b = '测试数据13';  

  

COMMIT;  

  

  

SELECT * FROM tbl1;  

SELECT * FROM mlog$_tbl1;   -- 对每条记录的update生成2条MV日志记录：当主键发生改变时，记录一对“D”、“I”型记录，表示先删除后插入；当主键不变时，记录一对“U”、“U”型记录，分别表示新值和旧值；  

  

-- 手工刷新MV  

EXEC dbms_mview.refresh('mv_tbl1');  -- 刷新MV，对MV日志进行归并处理，对每一个主键，只执行其归并后结果的操作  

SELECT * FROM mlog$_tbl1;  

  

-- 检查trigger测试表  

SELECT * FROM mv_tbl1_tri;  -- 对修改后的新主键13的所有后续修改只触发了1条insert；  

  

  

DELETE FROM tbl1;   -- 生成3条“D”MV日志记录  

COMMIT;  

  

SELECT * FROM tbl1;  

SELECT * FROM mlog$_tbl1;  

  

-- 手工刷新MV  

EXEC dbms_mview.refresh('mv_tbl1');  -- 刷新MV，触发3条delete，清空MV日志  

SELECT * FROM mlog$_tbl1;  

  

-- 检查trigger测试表  

SELECT * FROM mv_tbl1_tri;  

  

  

/***  

结论  

1. 在MV上可以建立触发器  

2. MV触发器基于刷新时间点的MV日志归并结果，在一些场景（只要记录两次刷新时间点数据的差异，不需要记录两次刷新之间的历史变化）可以简化应用处理。  

***/