SQLite / Geopackage AFTER INSERT trigger to calculate Length in QGIS

Question

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?

Answer 1

What is the error in my logic?

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

• old. is only available for DELETEs and UPDATEs
• new. is only available for INSERTs and UPDATEs

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):-

• First attempt ALL rows updated (incorrect)

• UPDATE .... WHERE .... Each row has been updated to change 10 to the length of the geom column (correct)

• nothing UPDATED??? (new geom row exists so IS in the table!!!!)

• result as the new geom row has been excluded from being considered in the WHEN clause BUT without the WHERE clause for the UPDATE ALL rows are updated (so 2 above is likely the best solution).

  • the demo uses the length function rather the the ST_length function as ST_length is not a standard built-in SQLite function.
    • hence the enclosures used around the length column to ensure that there is no ambiguity/conflict with the length function