|
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... Changed the trigger to statement level and everything works as expected as far as i can tell.
(03 May '12, 07:29)
Quick
Replies hidden
Did you have to fiddle around with the ORDER clause?
(03 May '12, 07:33)
Volker Barth
No, order is 1 on the statement trigger
(07 May '12, 07:28)
Quick
|
|
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.
Yes, that's what I would have expected - therefore I'm still somewhat puzzled that simply changing the trigger type should suffice as solution here. BTW: Thanks for linking the presentation in your blog:)
(03 May '12, 09:23)
Volker Barth
1
Elmi told me I should have been clearer above; what I meant to say is that the iMV trigger will fire after all other ROW-level triggers. Statement-level triggers fire after all row-level ones. Here is the section on "Advanced information on triggers" from the help: UPDATE statements can modify column values in more than one table. The sequence of trigger firing is the same for each table, but the order that the tables are updated is not guaranteed. For row-level triggers, BEFORE triggers fire before INSTEAD OF triggers, which fire before AFTER triggers. All row-level triggers for a given row fire before any triggers fire for a subsequent row. For statement-level triggers, INSTEAD OF triggers fire before AFTER triggers. Statement-level BEFORE triggers are not supported. If there are competing statement-level and row-level AFTER triggers, the statement-level AFTER triggers fire after all row-level triggers have completed.
(03 May '12, 09:36)
Glenn Paulley
Replies hidden
1
So you're now saying the iMV statement-level trigger will fire before any user-defined statement-level triggers? OK, then at least I do understand that changing the trigger type does work as desired.
(03 May '12, 09:44)
Volker Barth
|
