Does sqlite3 support a trigger to automatically update an 'updated_on' datetime field?

Question

I have a table that looks like this

user_id   |  name   |  created_on   |   updated_on
--------------------------------------------------
1         | Peter D | 1/1/2009      |

If I insert or update a record, I'd like a trigger to update the updated_on field with datetime('now'). But I can't find the function name to target the most recently updated row in sqlite3. Is there one?

Answer 1

You can specify a trigger to update the updated_on column of your row when the row changes, however, the change invokes the same trigger again and you'll end up with the error too many levels of trigger recursion.
In order to avoid that, you can specify which columns you want the trigger to fire upon and of course you need to exclude updated_on.

CREATE TRIGGER your_table_trig 
AFTER UPDATE OF ID, 
  user_id, 
  name, 
  created_on ON your_table 
FOR EACH ROW BEGIN UPDATE your_table 
SET updated_on = DATETIME ('NOW') 
WHERE rowid = new.rowid ; END;

Answer 2

CREATE TRIGGER your_table_trig AFTER UPDATE ON your_table
 BEGIN
  update your_table SET updated_on = datetime('now') WHERE user_id = NEW.user_id;
 END;