CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle11g
dhj
dhj

Reputation: 11

Oracle trigger to refresh materialised view is giving delayed output in materialised view

We have made a materialised view called 'Complaint_view'. We want to refresh this view whenever insert, delete or update is performed on 'Complaint' table. We used the following code to make the view...

create materialized view complaint_view 
build immediate
refresh complete
on demand
as
select t1.Complaint_ID,Status,C_TimeStamp,Description,House_No,Owner_name,Staff_ID,Staff_name from
(select Complaint_ID,Status,C_TimeStamp,Description,Complaint.House_No,Person.Name as Owner_name 
from Complaint
join House
on Complaint.House_No = House.House_No
join Person 
on House.Owner_ID = Person.Person_Id) t1
left outer join
(select Complaint_ID,Complaint.Staff_ID, Person.Name as Staff_name
from Complaint
join Person
on Complaint.Staff_ID = Person.Person_ID) t2
on t1.Complaint_ID = t2.Complaint_ID;

The select statement has a purpose and uses other tables as well.

I made the following trigger for it

create or replace trigger refresh_comp_view
after insert or update or delete on Complaint 
DECLARE    
   PRAGMA AUTONOMOUS_TRANSACTION; 
begin
dbms_mview.refresh(LIST=>'complaint_view');
commit;
end refresh_comp_view;

When I update the Complaint table, the view is updated, but previous update is being stored.

For example, if firstly I update a column to '123' and then again I update to '1234567', then in the materialised view, I would get '123' instead of '1234567'.

What is the problem here? Why previous value is being updated in the materialised view?

Upvotes: 0

Views: 174

Answers (1)

Justin Cave
Justin Cave

Reputation: 231791

First off, it really really sounds like what you want is an on commit materialized view. That's going to require that you have appropriate materialized view logs on the base tables. But since your materialized view definition just appears to contain joins, I don't see any obvious reason that it couldn't be done as an incremental refresh on commit.

As to your actual problem, ignoring the inefficiency of doing a complete refresh every time the data changes and the need to duplicate this trigger for the other tables that are referenced, the issue is that the trigger is an autonomous transaction. That means it completes separately from the transaction that did the DML and that it necessarily completes before that DML transaction completes. The dbms_mview.refresh call, therefore, sees the committed state of the tables when it runs which necessarily excludes the open transaction that caused the trigger to fire. So the refresh sees the committed value 123 rather than the currently uncommitted value of 1234567 when it runs.

If you really, really want to keep the current architecture for some reason, you'd want your trigger to submit a job that does the refresh only after the current transaction commits. You'd want to ensure that the trigger was not an autonomous transaction in order to ensure that the job doesn't start until some time after the current transaction commits.

Upvotes: 1

Related Questions