Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jose.

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

Answered by: Tom Kyte - Last updated: July 18, 2018 - 4:52 am UTC

Category: Database - 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 we 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.



and you rated our response

  (1 rating)

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

Reviews

Further question on materialized view

July 17, 2018 - 11:08 am UTC

Reviewer: Shveta from India

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

Followup  

July 18, 2018 - 4:52 am UTC

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

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here