[QGIS-Developer] SpatiaLite: SpatialIndex broken after trigger

[Prof. Dr. Jörg Höttges]

Hi,

There is a problem with the SpatialIndex and Triggers in SpatiaLite:

I have defined a trigger, that automaticly creates two geometry objects (geop: point object, geom: circle as multiline 
object), when a new dataset with attribute data is added to a table "schaechte".

After execution of an INSERT command you can see in QGIS the geometry objects only when no Spatial Index is created (see 
code below).

If I activate the CREATESPATIALINDEX command by uncommenting the respective line(s), the objects are not displayed in 
QGIS and the Spatial Index is broken after the insert executed by the trigger
SELECT CheckSpatialIndex('schaechte', 'geom') -->  0.

You can make them visible with:
SELECT RecoverSpatialIndex()

Does anybody know, why the SpatialIndex is broken after the INSERT command?

Here is the SQL-Code to be executed in the database window of QGIS or in SpatiaLiteGUI:

CREATE TABLE schaechte (
     pk INTEGER PRIMARY KEY,
     schnam TEXT,
     xsch REAL,
     ysch REAL,
     sohlhoehe REAL,
     deckelhoehe REAL,
     durchm REAL
);
SELECT AddGeometryColumn('schaechte','geop',25832,'POINT',2);
SELECT AddGeometryColumn('schaechte','geom',25832,'MULTIPOLYGON',2);
-- SELECT CreateSpatialIndex('schaechte', 'geop');-- geop objects are visible
SELECT CreateSpatialIndex('schaechte', 'geom');-- geom objects are not visible
CREATE TRIGGER IF NOT EXISTS create_missing_geoobject_schaechte
            AFTER INSERT ON schaechte FOR EACH ROW
         WHEN
             new.geom IS NULL AND
             new.geop IS NULL
         BEGIN
             UPDATE schaechte SET geop =
                 MakePoint(new.xsch, new.ysch, 25832)
             WHERE schaechte.pk = new.pk;
             UPDATE schaechte SET geom =
CastToMultiPolygon(MakePolygon(MakeCircle(new.xsch, new.ysch,
                     coalesce(new.durchm / 1000, 1), 25832)))
             WHERE schaechte.pk = new.pk;
         END;
INSERT INTO schaechte (schnam, xsch, ysch, sohlhoehe, deckelhoehe) VALUES
('D110036', 388798.830197, 5709945.16474, 79.51, 82.56),
('D110073', 388749.988968, 5709812.89315, 82.77, 85.47),
('D110074', 388709.61619, 5709930.66496, 80.82, 83.49),
('D110075', 388813.978304, 5709854.59264, 81.16, 84.09),
('D110076', 388809.473765, 5709888.00614, 80.49, 83.61);


Regards,

Jörg Höttges