The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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.

asked 22 Mar '12, 09:21

Martin's gravatar image

Martin
8.6k114149237
accept rate: 14%

edited 22 Mar '12, 09:23


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

permanent link

answered 22 Mar '12, 09:22

Breck%20Carter's gravatar image

Breck Carter
26.6k418575824
accept rate: 21%

...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

  • 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.

(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
showing 2 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 22 Mar '12, 09:21

question was seen: 5,468 times

last updated: 22 Mar '12, 14:06