I use SQL Anywhere 11.
I have the following view:
CREATE MATERIALIZED VIEW "DBA"."PointsAcc"( /* view_column_name, ... */ ) IN "SYSTEM" AS select sum(PL.Points) AS Sum, DP.UserUID, COUNT(*) AS cnt FROM Points DP KEY JOIN PointLine PL KEY JOIN PointHead PH GROUP BY DP.UserUID
I have a trigger in PointLine that should select the sum from this view:
CREATE TRIGGER "WorkOnPoints" AFTER INSERT, DELETE ORDER 1 ON "DBA"."Points" REFERENCING OLD AS oldrow NEW AS newrow FOR EACH ROW BEGIN declare @points integer; set @points = (isnull((select isnull(Sum, 0)) FROM PointsAcc where UserUID = newrow.UserUID), 0)) END;
The @points variable now contains the sum from before the line was inserted or deleted. How can i get the updated sum from the view in the trigger?
AFAIK, that won't do, as the mechanism to update an immediate materialized view does use statement-level AFTER triggers itself to update the view, and these will fire after your row-level AFTER trigger.
Confine the following contents from the Presentation "MERGE STATEMENT AND MATERIALIZED VIEWS IN SQL ANYWHERE" by Anil K. Goel (taken from slide 73), which describes how the maintenance of updating an iMV does work:
As to the order of different trigger types, here's the full doc page "Advanced information on triggers".
Based on that, I can't tell if a change of your trigger to a statement-level AFTER trigger would do, as it would need to be defined to work AFTER the internal ones... Using an appropriate (high) ORDER clause may or may not work...
As Volker outlines above, your AFTER ROW trigger will fire before the trigger to modify the materialized view fires. It has to be that way since an AFTER trigger may re-modify the values stored in the table, and the materialized view would also need to reflect those new values.
Changing the ORDER clause will affect the fire order of user after triggers, but the materialized view trigger will still be fired after all others.
answered 03 May '12, 07:35