|
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. |
|
Put the refresh in an EVENT and use TRIGGER EVENT in the trigger. ...and make sure the event closes immediately if it is not the only instance running...
(22 Mar '12, 10:08)
Volker Barth
Replies hidden
I was to quick, in accepting the answer, Trigger Event needs DBA authority, how to do this when the executing user is not DBA?
(22 Mar '12, 10:37)
Martin
Replies hidden
2
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.
(22 Mar '12, 10:45)
Breck Carter
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 :)
(22 Mar '12, 10:49)
Breck Carter
thanks that works
(22 Mar '12, 10:56)
Martin
I would bother when it is used inside a trigger (as it is the case here), as I would assume
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.
(22 Mar '12, 11:58)
Volker Barth
...at least that's the DEFAULT - cf. this other FAQ on the SQL SECURITY INVOKER clause... (which I have never used in production code...)
(22 Mar '12, 14:06)
Volker Barth
|
