oracle 如何使用触发器刷新物化视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13068366/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to refresh materialized view using trigger?
提问by manurajhada
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..
这是我用来刷新物化视图的 sql 触发器。但它说..
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.
PS：当表（物化视图使用）的数据进行任何DML操作时，触发器将被执行。
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'
回答by Justin Cave
It doesn't make sense to refresh a materialized view in a trigger.
在触发器中刷新物化视图没有意义。
You can resolve the syntax error by removing the word EXECUTE
您可以通过删除单词来解决语法错误 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 INSERTor an UPDATEagainst 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.
这将导致触发器编译。但是，当您尝试执行 anINSERT或 an UPDATEagainst 时tbl_contract，您现在将收到一个运行时错误，您不允许在触发器中提交，因为刷新物化视图会执行隐式提交，并且您无法在触发器内提交。
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.
您可能会尝试通过破坏事务完整性并在自治事务中进行刷新来解决该问题。这将消除 ORA-04092 错误，但随后物化视图不会将未提交的数据作为事务的一部分插入或更新，这些数据首先触发触发器，这违背了刷新物化视图的整个目的。
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 COMMITattribute on the materialized view, you'll want to look at the restrictions on fast refreshthat 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 procedureto tell you why the materialized view isn't eligible to be refreshed incrementally on commit.
正确的方法是首先不要使用触发器。正确的方法是定义物化视图以在提交时刷新自身 - REFRESH FAST ON COMMIT。由于您收到无法在ON COMMIT实体化视图上设置属性的错误，您需要查看数据仓库指南中列出的快速刷新限制，并确保您的实体化视图应该是可快速刷新的。然后，您可以使用dbms_mview.explain_mview 过程来告诉您为什么物化视图不符合在提交时增量刷新的条件。
回答by Anand Rathi
Please refer below link for other options
请参阅以下链接以了解其他选项
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:352126355153
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:352126355153
