I have a table in a SQLiteDatabase that has a date_last_modified column. Any time a row in this table is updated, I want to change the date_last_modified field to be the current time. I wrote a trigger that looks like this:
CREATE TRIGGER Patient_dlm_on_update After UPDATE ON Patient for each row when new.date_last_modified is null begin update Patient set date_last_modified = datetime('now', 'localtime') where rowid = old.rowid; end
But it does not seem to be working. Where have I gone wrong?
This table has a bunch of fields such as first_name, last_name, birthdate, etc. If I only update one of those fields, I want this trigger to run.
However, If I am updating the table and including a value for the date_last_modified field, then I don’t want to auto-update it.
never knew there was a trigger in SQL - that being said there is one thing.
CREATE TRIGGER Patient.dlm_on_update AFTER UPDATE ON Patient FOR EACH ROW WHEN
date_last_modified is null
update Patient set date_last_modified = datetime(‘now’, ‘localtime’) where rowid = old.rowid; end
not sure if you need NEW.rowid = old.rowid;
First off, I believe that your date_last_modified field will only get set once, since once the trigger is run, it will no longer be null. So I’d remove the ‘when new.date_last_modified is null’ portion.
I’ve never had to deal with your circumstance, so I can’t say whether it would work the way you have it, but would changing to a ‘…BEFORE UPDATE ON…’ do what you want?
CREATE TRIGGER Patient_dlm_on_update BEFORE UPDATE ON Patient for each row set date_last_modified = datetime('now', 'localtime')
I do using a lot of db triggers on SQLite and MySQL to backup records before delete or update, using the timestamps as Mark does above.
No issues so far and it’s a great functionality to keep track on everything you do in your database. Besides it’s handy to record creationtime and db-record-owner when adding a new record.
If you need copies or help, ask me next Monday.
Compare the old and new modified dates. They will match on a normal update.
If you add records with a specific date, the old and new shouldn’t match.
Something like this:
CREATE TRIGGER Patient_dlm_on_update AFTER UPDATE ON Patient FOR EACH ROW
WHEN new.date_last_modified=old.date_last_modified BEGIN
UPDATE Patient SET date_last_modified = CURRENT_TIMESTAMP
WHERE new.rowid = rowid;
Thanks @John A Knight, Jr - that is perfect. It works, but I don’t clearly understand why:
If I issue the following update statement:
update Patient set middle_name = "test2" where middle_name = "test"
Then as I understand things (and clearly I misunderstand) the “new” in the trigger does not have any date_last_modified value at all, so how can the clause of new.date_last_modified = old.date_last_modified be evaluated to true?
When submitting an update, do “old” and “new” refer to the entire row of the record you updated, regardless of the fields you actually altered?
During insert/update/delete, all columns from the affected row are available in the old/new references.
Yes. They refer to the row, not the modification. Old is the complete row prior to the update. New is the complete row after the update.