Do sqlite triggers trigger other triggers?

问题:

I am trying to implement the equivalent of the "ON UPDATE CURRENT_TIMESTAMP" MySQL feature in sqlite. My idea it to use a trigger like this:

CREATE TRIGGER last_update_trigger
AFTER UPDATE
ON mytable
FOR EACH ROW
BEGIN
UPDATE mytable SET last_update = CURRENT_TIMESTAMP WHERE id = old.id;
END

But there's a problem with this. Each time an update occurs on a record of this table, the trigger triggers a new update on this same record. This should trigger the trigger again, and again, leading to an infinite loop of updates.

Is this really what will happen? Will the update in my trigger trigger the trigger again? Can I avoid triggering of triggers within triggers?

回答1:

Actually, we're talking about SQLite, yes? Originally, recursive triggers (the infinite loop suspected above) were NOT supported specifically to prevent this problem. They're now supported, but turned off by default. You can, in theory, turn them back on with "PRAGMA recursive-triggers = true".

Horse's mouth is here: SQLite Online Doc

回答2:

Use a before update trigger:

CREATE TRIGGER last_update_trigger
BEFORE UPDATE
ON mytable
FOR EACH ROW
BEGIN
    set new.last_update = CURRENT_TIMESTAMP;
END;

Or, better yet, do this with a default value.

EDIT:

In SQLite, you could do this with an instead of trigger rather than a before trigger. Something like:

CREATE TRIGGER last_update_trigger
INSTEAD OF UPDATE
ON mytable
FOR EACH ROW
BEGIN
    update mytable
        set last_update = CURRENT_TIMESTAMP,
            col1 = new.col1,
            col2 = new.col2,
            . . .
END;

However, I think the better choice would be a default clause in the table definition.

回答3:

For UPDATE triggers, you can specify on which columns it should trigger. Just leave the last_update column out of that list:

CREATE TRIGGER MyTable_last_update
AFTER UPDATE OF col1, col2, etc ON MyTable
FOR EACH ROW
BEGIN
    UPDATE MyTable
    SET last_update = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

回答4:

John, use the WHEN clause, it's work even with recursive triggers on.

PRAGMA recursive_triggers=1;
CREATE TRIGGER IF NOT EXISTS "mytable_last_update"
    AFTER UPDATE ON main.mytable FOR EACH ROW
    WHEN NEW.last_update < OLD.last_update
BEGIN
    UPDATE mytable SET last_update=CURRENT_TIMESTAMP WHERE oid=OLD.oid;
END;

I'm using (strftime('%s','now')||substr(strftime('%f','now'),4)) instead CURRENT_TIMESTAMP, for using on web pages with Javascript Date() method.