13

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?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
lightly_recruited
  • 133
  • 1
  • 1
  • 5
  • http://www.sqlite.org/lang_createtrigger.html – Hassan Syed Dec 26 '09 at 20:12
  • Thanks Hassan, The issue is that I can't find a way, from within a trigger, to target the last updated row in sqlite3. I use 'last_insert_rowid()' in another trigger. But I'm looking for 'last_update_rowid()' and can't find anything something like it. – lightly_recruited Dec 26 '09 at 20:22
  • The answer provided by @DougCurrie works really good! It is a pitty that the SQLite documentation is not so clear [Create trigger SQLite](http://www.sqlite.org/lang_createtrigger.html) – Caumons Feb 02 '12 at 09:50

2 Answers2

32
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;
Doug Currie
  • 40,708
  • 1
  • 95
  • 119
  • If this answered your question it is customary on SO to "Accept" the answer by clicking on the checkmark next to the answer. – Jim Garrison Dec 27 '09 at 01:40
  • 2
    if I'm not wrong then it throws an error like - An error occurred while committing the data: too many levels of trigger recursion – Kishan Donga Feb 22 '19 at 07:25
  • You may need to set `sqlite3_limit(db,SQLITE_LIMIT_TRIGGER_DEPTH,...)` -- see https://www.sqlite.org/pragma.html#pragma_recursive_triggers – Doug Currie Feb 22 '19 at 15:58
  • See the answers to [this question](https://stackoverflow.com/q/6578439/1481479) for methods to avoid the trigger recursion issue without limiting trigger depth. – Danny Guo Jul 06 '20 at 18:42
2

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;
GDavoli
  • 517
  • 4
  • 8
  • 1
    This avoids the recursion problem, but introduces another one: you must remember to update the trigger every time you alter the schema. A nice workaround however when you know the schema won't change. – lonix Jan 02 '22 at 03:59