The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.8k422580826
accept rate: 20%

...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,487 times

last updated: 22 Mar '12, 14:06