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?

asked 03 May '12, 06:45

Quick's gravatar image

Quick
86227
accept rate: 0%

edited 03 May '12, 08:58

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274


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

permanent link

answered 03 May '12, 07:06

Volker%20Barth's gravatar image

Volker Barth
31.3k312458674
accept rate: 33%

edited 03 May '12, 07:12

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.

permanent link

answered 03 May '12, 07:35

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

...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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×61
×15

question asked: 03 May '12, 06:45

question was seen: 2,251 times

last updated: 07 May '12, 07:28