Can we create triggers on materialized views in oracle? If so, is a good practice to use them?
Yes we can create On updatable materialized views , if they use the "I_AM_A_REFRESH" function so as to not fire during a refresh.
I_AM_A_REFRESH Function
This function returns the value of the I_AM_REFRESH package state.
Syntax
DBMS_MVIEW.I_AM_A_REFRESH RETURN BOOLEAN;
Return Values
A return value of true indicates that all local replication triggers for materialized views are effectively disabled in this session because each replication trigger first checks this state. A return value of false indicates that these triggers are enabled.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#CEGHBIBJ
http://download.oracle.com/docs/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 we need to 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."
It 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. It was not recommended
Yes we can create On updatable materialized views , if they use the "I_AM_A_REFRESH" function so as to not fire during a refresh.
I_AM_A_REFRESH Function
This function returns the value of the I_AM_REFRESH package state.
Syntax
DBMS_MVIEW.I_AM_A_REFRESH RETURN BOOLEAN;
Return Values
A return value of true indicates that all local replication triggers for materialized views are effectively disabled in this session because each replication trigger first checks this state. A return value of false indicates that these triggers are enabled.
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_mview.htm#CEGHBIBJ
http://download.oracle.com/docs/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 we need to 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."
It 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. It was not recommended
