triggers on views

[mikedaruke]

Hello,

I am not sure if this is the correct way to do this or not, I am trying to have my views update one after another. With the basis being one view relies on another. Currently I have them setup to update at a certain time, but if one of the views take longer then expected things get messed up.

So 4 Materizlized views are like this.

1. Select max(date) from table
2. Select bla bla from View 1
3. Select bla bla from View 2.

I am thinking of some sort of trigger (which I have no experience doing) that will trigger the refresh of 2 when view 1 is done, and 3 when 2 is done.

does that make sense? Anyone know how to do this?

Thanks

 

[Dagon]

By "update", I assume you really mean "refresh" the materialized views. Why exactly do you think a trigger is the right solution for this ? Why can't you just right a PL/SQL script that refreshes all the views in the correct order and call that once a day ? Do you need to have the views refreshed every time the underlying table changes ?

 

[Dagon]

When will they allow editing on this site ? I mean "write a PL/SQL" script.

 

[mikedaruke]

Dagon,

I am not set on triggers or think its right! Thats what I am asking for advice. Hmm a Pl SQL script, I didnt think of that. That sounds like a good idea. The views just relie on each other and I have them set to refresh once a day. So I never did any PL SQL script, can you give me a start or sample code? I would appreciate it. Thanks again for your help.

 

[Dagon]

I would have thought you'd just need something like:

begin
dbms_mview.refresh(mat_view1, 'complete');
dbms_mview.refresh(mat_view2, 'complete');
dbms_mview.refresh(mat_view3, 'complete');
...
end;