When is a immediate materialized view updated?

[Former Member]

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?

[VolkerBarth · Accepted solution]

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:

• DML updates on T populate internal work table with delta_T

• Contains before and after rows of T
  

• Internal statement level AFTER triggers for each base table T with dependent immediate views

• Maintained transparently on demand
• Transient, in memory only
• Dynamic: created, and retained, when executing DML update on T
• Dropped during certain DDL operations
• SQL statements for propagating delta_T to delta_iMV and for applying delta_iMV to each iMV referencing T

---

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...

[Former Member · Accepted solution]

Changed the trigger to statement level and everything works as expected as far as i can tell.

[VolkerBarth · Accepted solution]

Did you have to fiddle around with the ORDER clause?

[Former Member · Accepted solution]

No, order is 1 on the statement trigger

[Former Member]

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.

[VolkerBarth]

...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:)

[Former Member]

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.

[VolkerBarth]

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.