Thanks Simon, I'll explain the use case (there is a bit of detail, but you did ask :-)

We are viewing a live underwater camera at sea. QGIS is used to save GPS point & time to a spatialite table (called gps_points), also recording other fields, including transect no & point_type, and data about what can be seen on the video.

The point_type values available include an "s"; identifying the point at the start of the transect, and an "f"; defining the point at the end of the transect, and there are a few others.

An after insert trigger on the gps_point table tests the point_type (when ...), if "s" then it inserts a new record in the main station (transect) table, populating the start fields. Another after insert trigger tests for an "f" record, and if so it updates the station table, populating the finish fields. This works well, and the order the triggers fire is immaterial. The user will always enter an "s" record before an "f" one which takes care of the order they will actually do anything.

To simplify the workflow, we have been asked if the system can auto populate some of the fields in the gps_points table - eg: if the scallop_count field is null, populate with 0 (done with a default in the table create statement). Also, to avoid having to repetitively enter fields such as the station_no every time, to auto populate this with the last recorded station_no. 

This can be done as a simple update sql in an after insert trigger (the example you asked about)- 
update gps_point set station_no = (select station_no 
                                   from gps_point
                                   where station_no not null
                                   order by datetime(gps_time) desc
                                   limit 1)
where station_no is null
  and datetime(gps_time) = datetime(new.gps_time);

The where clause ensures that only the current record will be modified, and only when the appropriate field is null.

This approach can apply to several fields, and will avoid lots of typing, hence potential typo's, at a time when the user is already busy, and will really help simplify the workflow and improve data quality.

An after insert trigger can do this very well (I don't think a before insert trigger can be used to set the values to insert where they are null, but could be wrong?), but it needs to fire before the trigger that writes to the station table, so the station table gets the filled in values from the gps_point table, not the record with nulls that will be filled afterwards if the triggers fire in the wrong order.

I can work around this by having the trigger populate the nulls in the gps_point table, then have case statements in the triggers that populate the station table that also populates the nulls, so it will fix the data when it fires if the other trigger has not already done so. But I'm hoping (not expecting!) a more elegant approach with sequential triggers is possible. Your reply suggests this is not possible.
   

Does this clarify the situation? We are developing a few sqlite3 (spatialite) databases using triggers to improve field data capture systems. It has proven very successful to date.

Thanks for your advice, it is appreciated!!


Brent Wood

Thanks Simon,

I have it working nicely with multiple triggers. The (commented) sql file to create the tables & triggers is about 350 lines long... got a bit interesting.

The approach I have taken is to have one trigger updating the missing values in the gps_points table. This is straightforward.

The other triggers which insert & update records in the station table with data from the gps_points table have a case statement in the insert/update for each column which may be null, if not null, just use the new value - in this case they have probably already been updated by the other trigger. 

If they are null, the case statement uses the same sql as in the other trigger, to replace the null with the correct value to write to the second table, this allows for the situation where the other trigger has not yet been invoked.

So both tables get the correct result irrespective of the order the triggers are fired off. A bit of redundancy, but a robust solution. Performance is near enough to instantaneous from a user perspective. 

QGIS works brilliantly as the UI, spatialite as an pretty intelligent back end with triggers implementing the business rules.


One additional bit of complexity that works really well... There is a trigger_config table with one record per trigger, which stores a value that a trigger can use, as well as a flag determining whether the particular trigger is enabled. So the one database can be set up with different triggers, which can be turned off or on as each particular use case requires. Instead of a maintaining a dozen custom databases, each for a different type of survey, one (more complex but more flexible) database can be set up to support all use cases. 

One example - a given survey may have a transect 0.2nm long. A trigger can generate a 0.2nm circle (buffer) around the start point. QGIS automatically refreshes this table in the map, so the user can see the vessel position (GPS) on the map, and simply by saving a start point, they have a range ring around that position to show when the required distance has been traversed. Different surveys can store a different distance in the config table, and disable the trigger if it is not relevant in a particular survey. The config table can be opened and edited in QGIS to control the system as required... 

The combination of QGIS with a USB gps & spatialite with triggers is pretty powerful.

Thanks for your insights, they helped me decide how best to implement this functionality.

Brent