Create trigger experiment on MV

-- Create table tbl1 (a NUMBER, B VARCHAR2 (20); CREATE UNIQUE INDEX tbl1_pk ON tbl1 (); alter table tbl1 ADD (CONSTRAINT tbl1_pl primary key (a); -- create a music video log, to refresh a single table aggregation VIEW quickly, you must specify the including new values clause create materialized view log on tbl1 including new values; -- create mvcreate materialized view mv_tbl1BUILD immediaterefresh faststart with TO_DATE ('2017-06-01 08:00:00 ', 'yyyy-mm-dd hh24: mi: ss') NEXT SYSDAT E + 1/24 ASSELECT * FROM tbl1; -- CREATE the trigger test table create table mv_tbl1_tri (a NUMBER, B VARCHAR (20), c VARCHAR (20 )); -- create triggerCREATE or replace trigger tri_mv after delete or insert or update on mv_tbl1 referencing new as new old as old for each rowbegin 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;/-- INSERT INTO tbl1 VALUES (1, 'test data') to add MV test table data '); insert into tbl1 VALUES (2, 'test data 2'); insert into tbl1 VALUES (3, 'test data 3'); COMMIT; SELECT * FROM tbl1; SELECT * FROM mlog $ _ tbl1; -- generate 3 "I" MV log records -- manually refresh MVEXEC dbms_mview.refresh ('mv _ tbl1'); -- refresh the mv and trigger 3 inser T, clear the MV log SELECT * FROM mlog $ _ tbl1; -- check the trigger test table SELECT * FROM mv_tbl1_tri; -- modify the MV test table data 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 = 'test data 13' WHERE a = 13; UPDATE tbl1 SET B = 'test data 3 'where a = 13; UPDATE tbl1 SET B = 'test data 13'; COMMIT; SELECT * FROM tbl1; SELECT * FROM mlog $ _ tbl1; -- generate two MV log records for the update of each record: when the primary key changes, record a pair of "D" and "I" type records, indicating that the record is deleted first and then inserted; when the primary key remains unchanged, a pair of "U" and "U" types are recorded, indicating the new value and old value respectively. -- refresh MVEXEC dbms_mview.refresh ('mv _ tbl1') manually '); -- refresh the MV and merge the MV logs. For each primary key, only SELECT * FROM mlog $ _ tbl1 for the merged result; -- check the trigger test table SELECT * FROM mv_tbl1_tri; -- only one insert is triggered for all subsequent modifications to the modified new primary key 13; delete from tbl1; -- three "D" are generated" MV log record COMMIT; SELECT * FROM tbl1; SELECT * FROM mlog $ _ tbl1; -- manually refresh MVEXEC dbms_mview.refresh ('mv _ tbl1'); -- refresh the mv and trigger three delete records, clear the MV log SELECT * FROM mlog $ _ tbl1; -- check the trigger test table SELECT * FROM mv_tbl1_tri;/*** Conclusion 1. you can create a trigger on the MV. 2. the MV trigger is based on the merge results of the MV logs at the refresh time point. In some scenarios, you only need to record the data difference between the two refresh time points and do not need to record the historical changes between the two refresh times) it simplifies application processing. ***/