Materialised Views do not have triggers. However, Updatable Materialized View does have triggers but it has a catch to it, it must be based upon a single table.
Based on more than one table
CREATE MATERIALIZED VIEW LOG ON EMP; CREATE MATERIALIZED VIEW mv_test REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM emp em JOIN DEPT de ON EM.DEPTNO = DE.DEPTNO;
ORA-12013: updatable materialized views must be simple enough to do fast refresh
Based on single table
CREATE MATERIALIZED VIEW mv_test REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT * FROM emp; Materialized View created.
Trigger
CREATE OR REPLACE TRIGGER test_tg BEFORE INSERT OR UPDATE OF ENAME, MGR ON MV_TEST REFERENCING NEW AS New OLD AS Old FOR EACH ROW DECLARE tmpVar NUMBER; BEGIN tmpVar := 0; NULL; -- do as per the logic EXCEPTION WHEN OTHERS THEN NULL; -- Consider logging the error and then re-raise RAISE; END test_tg; Trigger created.
If the requirement is to have historical data, why not consider a standard transactional table to have data persistence using a Stored Procedure which is possible to execute using scheduler jobs.
As you have cited querying a large table which has 100 million records, my reckoning would be to use FOLL ALL or BULK COLLECT or consider batch processing, needless to say; this is a different topic.
The below are pseudo codes for procedure and scheduler jobs, make changes as deemed necessary. Either use INSERT or MERGE
Procedure using INSERT
CREATE OR REPLACE PROCEDURE historical_records (p_emp_no emp.empno%TYPE) IS BEGIN FOR rec IN ( SELECT ename, mgr, SUM (sal) tot_sal FROM scott.emp WHERE empno = p_emp_no GROUP BY ename, mgr) LOOP INSERT INTO hist_table (empno, ename, mgr, sal_tot) VALUES (rec.empno, rec.ename, rec.mgr, rec.tot_sal); END LOOP; END;
Procedure using MERGE
CREATE OR REPLACE PROCEDURE historical_records (p_emp_no emp.empno%TYPE) IS BEGIN MERGE INTO hist_table trg USING ( SELECT ename, mgr, SUM (sal) tot_sal FROM scott.emp WHERE empno = p_emp_no GROUP BY ename, mgr) src ON (trg.empno = src.empno) WHEN MATCHED THEN UPDATE SET trg.ename = src.ename, trg.mgr = src.mgr WHEN NOT MATCHED THEN INSERT (trg.empno, trg.ename, trg.sal_tot) VALUES (src.empno, src.ename, src.tot_sal); END;
Scheduler Job
BEGIN DBMS_SCHEDULER.CREATE_JOB ( job_name => 'HIST_PROC_JOB', job_type => 'PLSQL_BLOCK', JOB_ACTION => 'BEGIN historical_records; END;', start_date => SYSDATE, repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=05', end_date => NULL, enabled => TRUE, comments => 'Historical data insertion'); END; /
