Error refresh or update materialized view using trigger

Question

I have tried two approaches. First, I use stored procedure to be called in the trigger.

CREATE OR REPLACE TRIGGER MV_BANK_CODE_UPDATE
AFTER UPDATE OR INSERT OR DELETE OF APPROVAL_STATUS ON BANK_CODES 
FOR EACH ROW
  BEGIN


     SP_UPDATE_MV_BANK_CODES;


  END;
/

STORED PROCEDURE:

CREATE OR REPLACE PROCEDURE SP_UPDATE_MV_BANK_CODES IS


BEGIN

DELETE  MV_BANK_CODES;
COMMIT;
INSERT  MV_BANK_CODES 
(SELECT DISTINCT
       bankcodeeo.clearing_area,
       bankcodeeo.bank_code,
       bankcodeeo.bank_code_id,
       bankcodeeo.bank_name,
       bankcodeeo.office_code,
       bankcodeeo.bank_address,
          oldaccounteo.office_code
       || '.'
       || oldaccounteo.gl_account_no
       || '.'
       || oldaccounteo.sl_account_no
       || '.'
       || oldaccounteo.currency_code
       || '.'
       || oldaccounteo.department_code
       || '.'
       || oldaccounteo.working_unit_code
       || '.'
       || oldaccounteo.project_code
          AS new_ind_account_no,
       /*tcgleo.*/
       '9' AS position,
       transactioncodeeo.transaction_code,
       oldaccounteo.gl_account_no,
       oldaccounteo.sl_account_no,
       oldaccounteo.currency_code,
       oldaccounteo.department_code,
       oldaccounteo.working_unit_code,
       oldaccounteo.project_code,
       oldaccounteo.old_ind_account_no,
       oldaccounteo.old_ind_account_name,
       transactioncodeeo.forex_type_flag,
       transactioncodeeo.forex_type,
       glaccounteo.gl_account_type AS classification_flag,
       bankcodeeo.member_code,
       oldaccounteo.category_code,
       glaccounteo.normal_position,
       transactioncodeeo.source_account_flag,
       transactioncodeeo.usage_forex,
       bankcodeeo.member_type,
       TRANSACTIONCODEEO.CURRENCY_TYPE
  FROM bank_codes bankcodeeo
       INNER JOIN old_accounts oldaccounteo
          ON     bankcodeeo.account_no = oldaccounteo.old_ind_account_no
             AND oldaccounteo.approval_status = '2'
             AND oldaccounteo.activation_status = '1'
       INNER JOIN gl_sl_accounts glsleo
          ON     glsleo.gl_account_no = oldaccounteo.gl_account_no
             AND glsleo.sl_account_no = oldaccounteo.sl_account_no
             AND glsleo.approval_status = '2'
             AND glsleo.activation_status = '1'
       INNER JOIN gl_accounts glaccounteo
          ON     glaccounteo.gl_account_no = glsleo.gl_account_no
             AND glaccounteo.approval_status = '2'
             AND glaccounteo.activation_status = '1'
       INNER JOIN tc_gl tcgleo
          ON tcgleo.gl_account_no = glaccounteo.gl_account_no
       INNER JOIN transaction_codes transactioncodeeo
          ON     transactioncodeeo.transaction_code = tcgleo.transaction_code
             AND transactioncodeeo.approval_status = '2'
             AND transactioncodeeo.activation_status = '1'
 WHERE bankcodeeo.approval_status = '2'
       AND bankcodeeo.activation_status = '1');

       COMMIT;

END;
/

When I updated table BANK_CODE, I got an error:

ORA-01732: data manipulation operation not legal on this view 
ORA-06512: at "SP_UPDATE_MV_BANK_CODES", line 6 
ORA-06512: at "MV_BANK_CODE_UPDATE", line 3 
ORA-04088: error during execution of trigger 'MV_BANK_CODE_UPDATE'

Second, I used DBMS_MVIEW.REFRESH:

CREATE OR REPLACE TRIGGER MV_BANK_CODE_UPDATE_X
AFTER UPDATE OR INSERT OR DELETE OF APPROVAL_STATUS ON BANK_CODES 

FOR EACH ROW
  BEGIN

     DBMS_MVIEW.REFRESH('MV_BANK_CODES');

   END MV_BANK_CODE_UPDATE_X;
/

When I updated table BANK_CODE, I got an error:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3014
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2994
ORA-06512: at "MV_BANK_CODE_UPDATE_X", line 3
ORA-04088: error during execution of trigger 'MV_BANK_CODE_UPDATE_X'

What should I do?

Answer 1

May be i understand your requirement,you need to refresh the materialized view MV_BANK_CODES,whenever any dml operations happens on the table BANK_CODES.

Few points you must note

1. First of all no need to manually write code to refresh the MV using triggers.
2. We cannot do commit/rollback in triggers.
3. DBMS_MVIEW.REFRESH implicitly commits ,hence its not possible to commit inside trigger

BAD Solutions : You can do it by using PRAGMA AUTONOMOUS block inside the trigger,which treats the insert/update/delete inside the MV as separate transaction,and hence the mv will have uncommitted data,and if something fails afterward ,the mv data will not get rollbacked.

Preferrable solutons: Now if you want to refresh the materialized view, you need to find out how you define your materialized view Documentation Materialized View Refresh

create materialized view MV_BANK_CODES
  REFRESH FAST ON COMMIT
AS 
   SELECT DISTINCT
       bankcodeeo.clearing_area,
       bankcodeeo.bank_code,
       bankcodeeo.bank_code_id,
       bankcodeeo.bank_name,