SQLite Forum

create trigger
Login

create trigger

(1.1) Originally by andis59 with edits by Richard Hipp (drh) on 2020-04-22 20:42:49 from 1.0 [source]

I want to create two triggers, one for insert and one for update, that set a column to the current datetime. I have "found" these on the net and they seems to work but I need to ask about the WHERE part of the SQL.

 CREATE TRIGGER insert_process_order AFTER INSERT ON ProcessingOrders
 BEGIN
  UPDATE ProcessingOrders SET Created = DATETIME('NOW', 'localtime') WHERE ROWID = new.ROWID;
 END;

and

 CREATE TRIGGER update_process_order AFTER UPDATE ON ProcessingOrders
 BEGIN
   UPDATE ProcessingOrders SET Modified= DATETIME('NOW', 'localtime') WHERE ROWID = new.ROWID;
 END;

Why is the WHERE part needed? Isn't the trigger called for the row that has been inserted or updated? Also if the triggers are totally wrong please advise on how to make them better.

// Anders (Edit by drh: fixed formatting)

(2) By Keith Medcalf (kmedcalf) on 2020-04-22 21:04:13 in reply to 1.1 [link] [source]

The WHERE clause is needed in order to select WHICH ONE PARTICULAR ROW is to be updated.

UPDATE ProssessingOrders SET Modified=DATETIME('NOW','localtime');

would update ALL ProcessingOrders.Modified to the current localtime, rather than just the one row that has been modified. Give it a try.

Of course, the UPDATE in the triggers assumes that ROWID is the unique key for the table. In the absence of a column named ROWID and for a ROWID table, the name ROWID is a magical name that refers to the ROWID.

https://sqlite.org/rowidtable.html

(3) By Mark Lawrence (mark) on 2020-04-23 05:43:36 in reply to 1.1 [link] [source]

Keith has explained about the need for the WHERE clause in the UPDATE statements.

But you also need to keep in mind that statements within a trigger can make other triggers fire[1].

So for your examples above when the UPDATE inside insert_process_order runs it may cause the update_process_order trigger to fire as well, updating the same row again.

You can add a WHEN clause to make triggers conditional (the below is untested):

CREATE TRIGGER
    update_process_order
AFTER UPDATE ON
    ProcessingOrders
FOR EACH ROW WHEN
    OLD.modified != DATETIME('NOW','localtime')
BEGIN
    UPDATE ProcessingOrders
    SET Modified = DATETIME ('NOW', 'localtime')
    WHERE ROWID = new.ROWID;
END;

[1] I can't find a better documentation source than this at the moment: https://www.sqlite.org/pragma.html#pragma_recursive_triggers

(4) By andis59 on 2020-04-23 07:16:06 in reply to 1.1 [link] [source]

OK, so the trigger has no knowledge of the row that has triggered it.

Thanks

(5) By andis59 on 2020-04-23 07:16:55 in reply to 3 [link] [source]

Now that you mention it, it seems logical. I would never have realized this on my own!

Thanks

(6) By andis59 on 2020-04-23 08:20:40 in reply to 3 [link] [source]

After a bit of trying I found that without WHEN the Modified column is updated when the INSERT trigger is run.

But after adding WHEN OLD.Modified != DATETIME('NOW','localtime') there is never any update to the Modified column!

Any clue what's wrong?

(7) By andis59 on 2020-04-23 08:48:11 in reply to 6 [link] [source]

I found the problem NULL is not different from DATETIME('now','localtime')

so I changed the condition to WHEN OLD.Modified is NULL OR OLD.Modified != DATETIME('NOW','localtime')

But then it will update the Modified column on insert

So i might as well not include the WHEN statement...

(8) By Keith Medcalf (kmedcalf) on 2020-04-23 11:13:45 in reply to 4 [link] [source]

Sure it does have knowledge of the row which caused the trigger to execute.

The magic table name "new" refers to new values in the row and the magic table name "old" refers to the old values in the row, that caused the trigger to fire.

For an INSERT there are only "new" values, since the row did not exist prior to being inserted, hence there are no "old" values.

For DELETE triggers there are only "old" values because after the row is deleted it no longer exists and has no "new" values.

For UPDATE triggers there are both "old" (before the update) and "new" (after the update) values.

If you try to refer to "old" or "new" where they do not exist you should get an error when you attempt to prepare the statement which would fire that trigger since it references a table that does not exist.

Unless, of course, you have a table called "old" or "new" (or "excluded") in which case I have no idea what would happen. You could try and see, or just not name things such that they conflict with the names of builtin things. My reasonable expectation is that the "new", "old", and "excluded" table names would obscure tables by the same name.

(9) By Keith Medcalf (kmedcalf) on 2020-04-23 11:30:57 in reply to 7 [link] [source]

datetime('now', 'localtime') is always a text value.
NULL compared to anything is always NULL.
A NULL condition is false.

WHEN OLD.Modified is NULL OR OLD.Modified != DATETIME('NOW','localtime')
is just a long drawn out way of saying
WHEN OLD.Modified IS NOT DATETIME('NOW', 'localtime)