2

This is my first attempt at creating a trigger, so apologies if its obvious.

In QGIS 3.14, I have two SpatiaLite layers [Tree] and [Crown]. I am trying to create a trigger to insert a record into [Crown] after_insert on tree. I want the crown to be a buffer of size [spread], which is an attribute of [Tree] on the geometry of the [Tree].

Currently I have:

CREATE TRIGGER insert_crown AFTER INSERT ON Tree
BEGIN 
INSERT INTO Crown (treeid, amenity, geometry) 
SELECT uuid, amenity, buffer(geometry,spread) from Tree; 
END

But this creates a new crown for every record in Tree. Not just the record created (/inserted)

How do I restrict this to use only the record (tuple) from Trees that is being inserted.

1 Answer 1

2

What you are looking for is the NEW key word.

Example

CREATE TRIGGER insert_crown AFTER INSERT ON Tree
BEGIN 
INSERT INTO Crown (treeid, amenity, geometry) 
SELECT uuid, amenity, buffer(geometry,spread) from Tree
WHERE uuid = NEW.uuid; 
END

or you can do :

CREATE TRIGGER insert_crown AFTER INSERT ON Tree
BEGIN 
INSERT INTO Crown (treeid, amenity, geometry) 
VALUES (NEW.uuid, NEW.amenity, buffer(NEW.geometry,NEW.spread));
END

Documentation

Sqlite trigger documentation

1
  • Many thanks lejedi76
    – Oisin
    Jan 29, 2021 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.