如何使用触发器刷新实例化视图? [英] How to refresh materialized view using trigger?

问题描述

create or replace TRIGGER REFRESH_REST_VIEW
 AFTER
  INSERT OR UPDATE
 ON tbl_contract
BEGIN
    execute DBMS_MVIEW.REFRESH('REST_VIEW');
END REFRESH_REST_VIEW;
commit;

这是我用来刷新实例化视图的SQL触发器.但它说..

Warning: execution completed with warning
TRIGGER REFRESH_REST_VIEW Compiled.

P.S. :当表的数据(由Materialized View使用)进行任何DML操作时，将执行触发器.

我已经在Google上搜索了足够多的内容，很多帖子都说有可能，但我没有做到这一点.我尝试使用常规的触发器语法，但不起作用.

已更新:

现在我正尝试使用Procedure和Trigger..

create or replace
PROCEDURE Rfresh_mate_views AS
  BEGIN
   DBMS_MVIEW.REFRESH('REST_VIEW');
  END Rfresh_mate_views;


create or replace trigger refresh_company_mview
after insert or update ON BCD.BCD_COMPANY
begin
RFRESH_MATE_VIEWS(); 
end refresh_company_mview;

所有内容均已成功编译，但在表中进行更新时显示:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2449
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at "BCD.RFRESH_MATE_VIEWS", line 3
ORA-06512: at "BCD.REFRESH_COMPANY_MVIEW", line 2
ORA-04088: error during execution of trigger 'BCD.REFRESH_COMPANY_MVIEW'

解决方案

在触发器中刷新实例化视图没有意义.

您可以通过删除单词EXECUTE

来解决语法错误.

create or replace TRIGGER REFRESH_REST_VIEW
 AFTER
  INSERT OR UPDATE
 ON tbl_contract
BEGIN
    DBMS_MVIEW.REFRESH('REST_VIEW');
END REFRESH_REST_VIEW;

这将导致触发器编译.但是，当您尝试对tbl_contract执行INSERT或UPDATE时，现在会出现运行时错误，不允许您在触发器中提交，因为刷新实例化视图会隐式执行提交，您将无法在触发器内提交.

SQL> create table foo( col1 number );

Table created.

SQL> create materialized view mv_foo
  2  as
  3  select *
  4    from foo;

Materialized view created.

SQL> create trigger trg_foo
  2    after insert or update on foo
  3  begin
  4    dbms_mview.refresh( 'MV_FOO' );
  5  end;
  6  /

Trigger created.

SQL> insert into foo values( 1 );
insert into foo values( 1 )
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2760
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at "SCOTT.TRG_FOO", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_FOO'

您可能会尝试通过破坏事务完整性并在自主事务中进行刷新来解决此问题.这样可以消除ORA-04092错误，但是实体化视图不会包含作为事务的一部分插入或更新的未提交数据，该事务首先触发了触发器，从而破坏了刷新实体化视图的整个目的. /p>

正确的方法不是一开始就使用触发器.正确的方法是定义实例化视图以在提交时刷新自身-REFRESH FAST ON COMMIT.由于出现错误，您无法在实例化视图上设置ON COMMIT属性，因此，您需要查看 dbms_mview.explain_mview过程来告诉您，为什么物化视图不符合条件在提交时逐步刷新.

create or replace TRIGGER REFRESH_REST_VIEW
 AFTER
  INSERT OR UPDATE
 ON tbl_contract
BEGIN
    execute DBMS_MVIEW.REFRESH('REST_VIEW');
END REFRESH_REST_VIEW;
commit;

This is my sql trigger i am using to refresh Materialized View. But it says..

Warning: execution completed with warning
TRIGGER REFRESH_REST_VIEW Compiled.

P.S. : The trigger will be executed when the data of table (used by Materialized View) takes any DML operation.

I have googled enough, many post says it is possible but I am not getting how to do it. I tried with regular trigger syntax and it doesn't works.

Updated:

Now i am trying to the same with Procedure and Trigger..

create or replace
PROCEDURE Rfresh_mate_views AS
  BEGIN
   DBMS_MVIEW.REFRESH('REST_VIEW');
  END Rfresh_mate_views;


create or replace trigger refresh_company_mview
after insert or update ON BCD.BCD_COMPANY
begin
RFRESH_MATE_VIEWS(); 
end refresh_company_mview;

All has been compiled successfully but while updating in the table it says:

ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2449
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2429
ORA-06512: at "BCD.RFRESH_MATE_VIEWS", line 3
ORA-06512: at "BCD.REFRESH_COMPANY_MVIEW", line 2
ORA-04088: error during execution of trigger 'BCD.REFRESH_COMPANY_MVIEW'

解决方案

It doesn't make sense to refresh a materialized view in a trigger.

You can resolve the syntax error by removing the word EXECUTE

create or replace TRIGGER REFRESH_REST_VIEW
 AFTER
  INSERT OR UPDATE
 ON tbl_contract
BEGIN
    DBMS_MVIEW.REFRESH('REST_VIEW');
END REFRESH_REST_VIEW;

That will cause the trigger to compile. However, when you try to execute an INSERT or an UPDATE against tbl_contract, you'll now get a runtime error that you are not allowed to commit in a trigger because doing a refresh of a materialized view does an implicit commit and you cannot commit inside a trigger.

SQL> create table foo( col1 number );

Table created.

SQL> create materialized view mv_foo
  2  as
  3  select *
  4    from foo;

Materialized view created.

SQL> create trigger trg_foo
  2    after insert or update on foo
  3  begin
  4    dbms_mview.refresh( 'MV_FOO' );
  5  end;
  6  /

Trigger created.

SQL> insert into foo values( 1 );
insert into foo values( 1 )
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2760
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at "SCOTT.TRG_FOO", line 2
ORA-04088: error during execution of trigger 'SCOTT.TRG_FOO'

You could potentially try to resolve that by blowing away your transactional integrity and doing the refresh in an autonomous transaction. That will eliminate the ORA-04092 error but then the materialized view won't have the uncommitted data that was inserted or updated as part of the transaction that fired the trigger in the first place which defeats the whole purpose of refreshing the materialized view.

The proper approach is not to use a trigger in the first place. The proper approach is to define the materialized view to refresh itself on commit-- REFRESH FAST ON COMMIT. Since you are getting an error that you cannot set the ON COMMIT attribute on the materialized view, you'll want to look at the restrictions on fast refresh that are listed in the Data Warehousing Guide and make sure your materialized view should be fast-refreshable. Then, you can use the dbms_mview.explain_mview procedure to tell you why the materialized view isn't eligible to be refreshed incrementally on commit.

这篇关于如何使用触发器刷新实例化视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！