0

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?

Craig Tullis
  • 9,939
  • 2
  • 21
  • 21
klundby
  • 301
  • 1
  • 3
  • 17
  • The solution is a statementlevel trigger. SQL Anywhere uses row leval triggers to update the view and "user" triggers are run befor this. – klundby May 03 '12 at 11:36

1 Answers1

1

It depends on the Version of SQLA you have currently use.

If you did not create the View with immediate refresh option you have to call

REFRESH MATERIALIZED VIEW view-list

For more details check out these Links

http://dcx.sybase.com/index.html#1201/en/dbreference/alter-materialized-view-statement.html

And

http://dcx.sybase.com/1201/en/dbreference/refresh-materialized-view-statement.html

TDuemesnil
  • 36
  • 1