Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jose.

Asked: February 19, 2008 - 4:21 am UTC

Last updated: July 18, 2018 - 4:52 am UTC

Version: 9.2.0.6

Viewed 10K+ times! This question is

You Asked

Are triggers on materialized views supported by oracle?
If so, is a good practice to use them?

and Tom said...

On updatable materialized views - yes, if they use the

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_mview.htm#CEGHBIBJ

I_AM_A_REFRESH function so as to not fire during a refresh.

See:
http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref7906

"Note:
If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the DBMS_MVIEW procedure I_AM_A_REFRESH returns TRUE."

which effectively means that for a read only materialized view, the only time the trigger would fire would be during the refresh process - and you would have logic that causes your trigger to just exit at that point (it would never do anything)



Triggers on a read only materialized view might appear to work - but you cannot rely on them working.

An update of a row in a materialized view might be done as a DELETE+INSERT.

A refresh of a materialized view might involve a delete + insert of every row.

A refresh might involve a truncate plus direct path load of ever row.

(the last two will probably happen sometime, you would lose anything your trigger had done in the past)


You do not know how we refresh, it would be a very shaky proposition to have a trigger on a read only materialized view - I would not recommend it.



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Further question on materialized view

Shveta, July 17, 2018 - 11:08 am UTC

I have a functionality where MV (tab_v) is created on Prebuilt table (tab_v). MV is getting refreshed from other table (table2).
I have written a trigger on the table tab_v that
If data is inserted in the table tab_v , the value will be stored as 'I' in some other table (table3).
If the data is updated, the value will be stored as 'U' in some other table (table3).
If the data is deleted, the value will be stored as 'D' in some other table (table3).

As per your response, Mv understands Update as Delete + Insert. And when the trigger is getting fired during Update. The value inserted in table3 is D and I. And not U.

Please tell me the solution.
Connor McDonald
July 18, 2018 - 4:52 am UTC

Please log that as a new question with a complete test case.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library