1

I didn't find a solution on this so far. Seems like there is not that much SQLite trigger stuff out there.

I'm trying to archive an update of a column if it is empty on update. In MySQL I probably would do something like NEW.updated_at = now()

CREATE TRIGGER test_in
BEFORE UPDATE ON test
FOR EACH ROW
   WHEN (NEW.updated_at IS NULL)
BEGIN
   NEW.updated_at = now();
END;

But I read that you can't modify NEW in SQLite. And the value of NEW.updated_at in After-Trigger is already set with old value. Is there a way to temporarily store the field and use it in After-Trigger? OR prevent it from being overridden in Before-Trigger.

How could I do this in SQLite?

EDIT: My original trigger looks like this:

CREATE TRIGGER IF NOT EXISTS UpdatedAt_Update_${name}
AFTER UPDATE ON ${name}
FOR EACH ROW
    WHEN OLD.updated_at = NEW.updated_at
BEGIN
    UPDATE ${name}
    SET updated_at = strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')
    WHERE rowid = OLD.rowid;
END;

The problem is, that if I do an update with the same updated_at it's replaced by a new one.

EDIT 2: TL;DR

What I want to archive on update

  • new value updated_at given: use given value
  • no value updated_at given: use current microtime

EDIT 3: So I tried as @LS_ᴅᴇᴠ suggested.

CREATE TRIGGER test_in
AFTER UPDATE OF id, content ON test
FOR EACH ROW
  WHEN NEW.updated_at IS NULL
BEGIN
  UPDATE test
    SET updated_at = strftime('%Y-%m-%d %H:%M:%f', 'now', 'localtime')
    WHERE id = NEW.id;
END

This sets updated_at to current time when the column is empty. But only once. On next time it keeps updated_at when no new value is given. I'm testing this with SQLite Database Browser. But it also didn't work on Android using a SQLite plugin for cordova.

pixelarbeit
  • 484
  • 2
  • 11
  • There is no such need to use trigger for the same. You can define default clause at table definition or use Update statement to achieve the same result. – Ankit Bajpai Dec 20 '17 at 08:35
  • The problem is that I need i microtime timestamp which is not supported by SQLite as default. I only used `now()` for simpler testing. – pixelarbeit Dec 20 '17 at 08:55
  • 1
    You can use microtimestamp in table def: https://stackoverflow.com/questions/17574784 – LS_ᴅᴇᴠ Dec 20 '17 at 10:17

2 Answers2

0

If you want to change some value in the table, you must execute another UPDATE statement afterwards. To check whether the original update changed the column, you have to compare the old and new values. (And to prevent the trigger from calling itself recursively, restrict it to certain columns.)

CREATE TRIGGER test_in
AFTER UPDATE OF (other, columns) ON test
FOR EACH ROW
WHEN NEW.updated_at IS OLD.updated_at
BEGIN
    UPDATE test
    SET updated_at = now()
    WHERE id = NEW.id;
END;
CL.
  • 173,858
  • 17
  • 217
  • 259
  • My edit was a bit too late. The problem remains, when I update values and `OLD.updated_at = NEW.updated_at` it gets overwritten. New `updated_at` should only be set, if it wasn't given in the updated statement. – pixelarbeit Dec 20 '17 at 09:04
  • I forgot that the default value only applies the first time. What I want to archive is that it is updated every time, when there is no field `updated_at` given. In MySQL I could use `ON UPDATE CURRENT_TIMESTAMP(6)` for it. – pixelarbeit Dec 20 '17 at 15:42
0

If you want "update of a column if it is empty on update", then your when must be:

...
    WHEN OLD.update_at IS NULL
...

But I suggest change requirement to update column if no new data is specified:

...
    WHEN NEW.update_at IS NULL
...

Anyways, as I already comment, you can have microsecond timestamp with plain SQLite.

LS_ᴅᴇᴠ
  • 10,823
  • 1
  • 23
  • 46
  • Will check the solution you linked in the comment. Problem with `WHEN NEW.updated_at IS NULL` is that when `updated_at` was already set before, but is null in the current statement it will get the old value. Problem with `WHEN OLD.updated_at IS NULL` is that isn't what I want :D – pixelarbeit Dec 20 '17 at 14:39
  • So, just remove `WHEN ...`. But notice your requirements: "I'm trying to archive an update of a column if it is empty on update". – LS_ᴅᴇᴠ Dec 21 '17 at 10:38
  • Not sure how removing `WHEN ...` should help, Check my 2nd edit for maybe easier explanation. – pixelarbeit Dec 21 '17 at 15:43
  • Ok, using `WHEN NEW.update_at IS NULL` will accomplish 2nd edit. Notice that when no `update_at` is supplied, current time is used, regardless of old value. – LS_ᴅᴇᴠ Dec 21 '17 at 17:13
  • Hm, I already tried this but it didn't work. I will check this again – pixelarbeit Dec 22 '17 at 09:43
  • So I tried again and its not working. See Edit 3. I will probably just solve it by setting updated_at every time in my code. – pixelarbeit Dec 22 '17 at 09:56