SQLite 3.8 timestamp update trigger issue

Question

I just recently started using SQLite Studio 3.0.5, just to try it out, I've been using SQLiteSPY 1.9.6 for a while now, with almost never any issues.

Here is a simple dictionary table with two triggers that execute perfectly.

CREATE TABLE IF NOT EXISTS tbl_english_dictionary (
word_id INTEGER PRIMARY KEY AUTOINCREMENT,
word VARCHAR(50),
definition TEXT,
notes TEXT,
lm_datetime TEXT);

CREATE TRIGGER insert_tbl_english_dictionary AFTER INSERT ON tbl_english_dictionary
BEGIN
    UPDATE tbl_english_dictionary SET lm_datetime = datetime('now', 'localtime')
    WHERE rowid = new.rowid;
END;

CREATE TRIGGER update_tbl_english_dictionary BEFORE UPDATE ON tbl_english_dictionary
BEGIN
    UPDATE tbl_english_dictionary SET lm_datetime = datetime('now', 'localtime')
    WHERE rowid = new.rowid;
END; 

When executing an update in SQLiteSPY it works perfectly, I see the new data, and the updated timestamp.

When executing the update in SQLiteStudio...I get : 'Too many levels of trigger recursion'

So I went searching and found a few suggestions about changing the code for my trigger,...such as adding...: WHEN NEW.lm_datetime < OLD.lm_datetime in the Pre-condition as such :

CREATE TRIGGER update_tbl_system_messages
    BEFORE UPDATE
        ON tbl_system_messages
  FOR EACH ROW
      WHEN NEW.lm_datetime < OLD.lm_datetime
BEGIN
   UPDATE tbl_system_messages
      SET lm_datetime = datetime('now', 'localtime') 
    WHERE message_id = OLD.message_id;
END;

I have tried editing the SET statement from

SET lm_datetime = datetime('now', 'localtime') 

to

SET lm_datetime = CURRENT_TIMESTAMP;

I noticed that by added the pre-condition statement I was able to stop the recursion error, my update takes place...but the timestamp field does not get updated...as if the trigger is non-existant.

The database file was actually created by a python script, I am running python 3.4

SQLite Studio shows me that my DB version is : 3.8.7.4 SQLiteSPY shows me that my DB version is : 3.8.0.2

I won't even get into that one...but if anyone has ideas on this UPDATE TRIGGER I am all ears.

I could just simply say...if it's not broken don't fix it...because it works in SQLiteSPY no problem.... but it would be nice to solve this.

Thank you.

Answer 1

You do not want to run the UPDATE trigger when you are updating the timestamp column, so you should restrict the trigger to the other columns:

CREATE TRIGGER ...
AFTER UPDATE OF word, definition, notes ON tbl_english_dictionary
BEGIN ...