Help With A Trigger

Databases
1

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.

2

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
begin
update Patient set date_last_modified = datetime(‘now’, ‘localtime’) where rowid = old.rowid; end

not sure if you need NEW.rowid = old.rowid;

3

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')
4

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.

5

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; 
   END
6

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?

7

During insert/update/delete, all columns from the affected row are available in the old/new references.

8

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.