Adding triggers to SpatiaLite Databases in QGIS

2 minutes read

There exist various data management methods in QGIS. A data source can be a file, a directory, a protocol or a database. SpatiaLite is database that is widely used in QGIS. You can store layers into this database and edit these layers directly in your QGIS project. During digitization, there are many actions that get triggered in the database such as create or delete logs. These actions are recorded in the database but do not reflect in the individual layers being digitized.
How do we do this in our layers/tables?
This post will demonstrate how to add triggers into the SpatiaLite layers so as to automate attribute creation and recording.

Requirements
Have a SpatiaLite database in QGIS. To know more about SpatiaLite databases, check out the SpatiaLite Databases post

Create a SpatiaLite Layer

Go to Layer Menu > Create Layer > New SpatiaLite layer

A new SpatiaLite layer dialog will appear.

  • Choose the Database connection
  • Give a name to your layer
  • Choose Geometry type. In this case, I choose Point
  • Add a field name, choose type and hit Add to fields list

In this case, we will be adding an automatic date trigger(To populate the field with datetime of digitization)
Click Ok to create the new layer. Your layer should be added to your QGIS project(Layers panel)

If you try to digitize features and save the changes without adding text to respective fields, the name and date_rec fields will be empty or null.

Create Trigger

Open DB Manager (Database Menu > DB Manager > DB Manager
Select the database connection to use under SpatiaLite section on the left side of the dialog box.
While the database is selected, click on the SQL Window button(Has a settings/hammer icon) to open the Query window.

On the SQL window, this is where you need to add the commands. To create the trigger, add

CREATE TRIGGER insert_date AFTER INSERT ON cities

This will create a trigger with the name insert_date to cities table.
Then add this line to do the actual job of updating the date_rec column when one digitizes a new feature.

BEGIN
UPDATE cities SET date_rec = DATETIME('NOW') WHERE rowid = new.rowid;
END

This command enables the trigger to add current date and time to the date_rec column whenever a new row is added to the layer during digitization and changes have been saved.

Click Execute to apply the trigger.

Get back to your map canvas and digitize a number of features and save changes. Check the attribute table for changes. Your column should be populated with dates and time of digitization as shown below;

That’s it. We have added a trigger that adds the date and time automatically when one digitizes the features.

You can also watch a video of the same at Life in GIS Channel

To Do:
Try adding more triggers to your layer and try them out in your project. Save time and efforts with triggers.

Happy Hacking!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.