[Qgis-user] spatialite with update trigger seems to result in 'unselectable' geometry

[Janneke van Dijk]

Dear list,

Could anybody maybe help me understand what I'm doing wrong (or even 
tell me that what I want to do can't be done)?

This is what I'm trying to do. I have a spatialite layer with columns 
north and east which contain coordinates in decimal degrees. I would 
like to be able to change the coordinates in the attribute table and 
have the trigger take care of updating the geometry based on the new 
coordinates. The following update statement, using the DB manager:

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

works as expected: after saving the edits, the point will appear in the 
new location based on the changed coordinates, and I can select the 
point in the map and I can use the identify button to open the form at 
that point. If (by way of a test) I use the same statement inside a 
trigger (and I realise this is not ultimately how I would want to define 
the trigger, but I'm trying to understand the different effects of the 
same update statement):

CREATE TRIGGER update_geom AFTER UPDATE ON watersources BEGIN

UPDATE watersources SET geom = Transform(MakePoint(east, north, 4326), 
32638) WHERE id=1678;

END

I can change the coordinates, save the changes and stop editing, the 
point will indeed appear in the new location, but I can't select it in 
the map, or use the identify feature to open the form. I can locate it 
in the attribute table and select it and use 'zoom to selected' after 
which the map will center on the selected point (but not zoom in). After 
running the update statement 'stand alone' again, all works as expected 
again.

Does anyone have an idea why this could happen? Has anyone tried doing 
something similar in spatialite and gotten it to work? Any tips on how I 
could troubleshoot this? I have a test file with only 4 points that I 
could share if that helps. Using QGIS 3.4.4. on Windows 10.

Any help/thoughts will be appreciated!

Thanks,

Janneke



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20190205/6c8e0d7d/attachment.html>