SQLite User Forum: create trigger

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