How to use REFRESH MATERIALIZED VIEW in trigger?

[#1]

For a materialized view which is in manual refresh mode, what is the best approach to use REFRESH MATERIALIZED VIEW from inside a trigger.

REFRESH MATERIALIZED VIEW will commit the data, which for me broke the overall logic.

So how to overcome this limitation and still refresh the view triggered by a trigger.

Immediate refresh mode is not possible to use, because my materialized view has no unique index column.

[Breck_Carter · Accepted solution]

Put the refresh in an EVENT and use TRIGGER EVENT in the trigger.

[VolkerBarth · Accepted solution]

...and make sure the event closes immediately if it is not the only instance running...

[MCMartin · Accepted solution]

I was to quick, in accepting the answer, Trigger Event needs DBA authority, how to do this when the executing user is not DBA?

[Breck_Carter · Accepted solution]

Put the TRIGGER EVENT inside a procedure owned by DBA, and GRANT EXECUTE on that procedure... procedures run with the permissions of the owner rather than the caller.

[Breck_Carter · Accepted solution]

Why bother? If you were running the REFRESH in some other non-EVENT context, would you bother checking to see if a REFRESH was already running? ...that's what locks and blocks are for 🙂

[MCMartin · Accepted solution]

thanks that works

[VolkerBarth · Accepted solution]

I would bother when it is used inside a trigger (as it is the case here), as I would assume

• that DML statements might be executed very frequently, and
• that the REFRESH is an expensive operation.

Additionally, I do assume that the trigger is a statement-level one, otherwise it could be called several times for one DML statement.

---

If this is a rarely modified table, well, then my point is surely moot.

[VolkerBarth · Accepted solution]

...at least that's the DEFAULT - cf. this other FAQ on the SQL SECURITY INVOKER clause...

(which I have never used in production code...)