sqliteqgisgeopackage

SQLite / Geopackage AFTER INSERT trigger to calculate Length in QGIS

I have been following the guide here: https://www.northrivergeographic.com/adding-triggers-to-geopackage/ to add a trigger to a SQLite database in a Geopackage file. Geometry type is Line (LineStringZM).

I want it to do is automatically calculate the Line length and update a field called Length when a new record in created, using Toggle Editing > Add Line Feature in QGIS Digitizing Toolbar. Easy enough... except that the trigger seems to update all of the records with the length of the latest line added.

My code below:

CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT ST_Length(new.geom) FROM WaterwayOptions );
END

I thought about adding in a WHEN old.geom <> new.geom filter (as per example here: https://www.sqlitetutorial.net/sqlite-trigger/) clause but this will fail when creating a new record.

What is the error in my logic?

Solution

  • What is the error in my logic?

    When inserting there is no old.? only new.? use column without the old. qualifier.

    Also your probably want a WHERE clause for the UPDATE rather then a WHEN clause to restrict when the trigger is triggered (which would also required a WHERE clause on the UPDATE anyway) e.g.

    UPDATE WaterwayOptions SET Length = (SELECT ST_Length(new.geom) FROM WaterwayOptions ) WHERE geom = new.geom;

    If you tried using the WHEN clause to try to see if the new geom was not previously existing then you would have an issue, as AFTER the insert it does exist as far as the transaction is concerned (BEFORE is warned against).

    If a more complex WHEN clause is used (see demo) that finds that the new geom row has been inserted (see demo) then the UPDATE would still update all rows.

    As such the WHERE clause on the UPDATE would be required as such the WHERE clause on the UPDATE is very likely the best solution.

    Perhaps consider the following demo that approximates what you appear to be doing.

    It goes through 4 TRIGGERS

    1. the original
    2. a second using the suggested WHERE clause,
    3. the third using a too simple WHEN clause, and finally
    4. the fourth using a more complex WHEN clause

    Other than creating the TRIGGERs the code is identical (INSERTs the same data)

    The demo code/SQL:-

    /* ATTEMPT 1 (original)*/
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT 'blah');

CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions );
END
;

INSERT INTO WaterwayOptions (woid,geom,`length`)
    VALUES
    ('wo1','geoma',10),
    ('wo2','geomaa',10),
    ('wo3','geomaaa',10)
;
SELECT * FROM waterwayoptions;

/* ATTEMPT 2 (WHERE clause for the UPDATE)*/
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT 'blah');
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions ) WHERE geom=new.geom;
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
    VALUES
    ('wo1','geoma',10),
    ('wo2','geomaa',10),
    ('wo3','geomaaa',10)
;
SELECT * FROM waterwayoptions;

/* ATTEMPT 3 (simple WHEN) */
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT 'blah');
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions WHEN new.geom NOT IN (SELECT geom FROM WaterwayOptions)
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions );
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
    VALUES
    ('wo1','geoma',10),
    ('wo2','geomaa',10),
    ('wo3','geomaaa',10)
;
SELECT * FROM waterwayoptions;


/* ATTEMPT 4 (more complex WHEN) */
DROP TABLE IF EXISTS WaterwayOptions;
CREATE TABLE IF NOT EXISTS WaterwayOptions (woId TEXT PRIMARY KEY, geom TEXT UNIQUE, `length` INTEGER, othercol TEXT DEFAULT 'blah');
DROP TRIGGER IF EXISTS update_Length_after_Insert;
CREATE TRIGGER update_Length_after_Insert AFTER INSERT
ON WaterwayOptions WHEN new.geom NOT IN (SELECT geom FROM WaterwayOptions WHERE geom <> new.geom)
BEGIN
UPDATE WaterwayOptions SET Length = (SELECT Length(new.geom) FROM WaterwayOptions);
END
;
INSERT INTO WaterwayOptions (woid,geom,`length`)
    VALUES
    ('wo1','geoma',10),
    ('wo2','geomaa',10),
    ('wo3','geomaaa',10)
;
SELECT * FROM waterwayoptions;

    The results (SELECTS after the INSERTS and therefore the TRIGGER):-

    enter image description here

    enter image description here

    enter image description here

    enter image description here