sqlite - 如何在插入时自动将日期设置为当前日期

Question

我有下表

CREATE TABLE ex(
 id INTEGER PRIMARY KEY,
 dat DATE NOT NULL CHECK(dat IS date(dat))
);

我希望在每次插入时自动将 dat 设置为 date('now')。
这就是我创建 TRIGGER 的原因

CREATE TRIGGER setTrigger
         AFTER INSERT
            ON ex
FOR EACH ROW
BEGIN   
    UPDATE ex   
        SET dat = date('now')
    WHERE id = NEW.id;
END;

但我收到以下错误:

too many levels of recursion

我怎样才能解决这个问题 ？

Answer 1

尝试删除 FOR EACH ROW，没有必要，这可能是递归以及 CHECK 约束的原因。

当然，您甚至不需要可以使用的 TRIGGER 或 CHECK 约束

CREATE TABLE ex(
 id INTEGER PRIMARY KEY,
 dat DATE DEFAULT CURRENT_DATE
);

注意只有两列你必须做 INSERT INTO ex (ID) VALUES(null);
您可能还希望考虑以下事项:-

Maximum Depth Of Trigger Recursion

SQLite limits the depth of recursion of triggers in order to prevent a statement involving recursive triggers from using an unbounded amount of memory.

Prior to SQLite version 3.6.18 (2009-09-11), triggers were not recursive and so this limit was meaningless. Beginning with version 3.6.18, recursive triggers were supported but had to be explicitly enabled using the PRAGMA recursive_triggers statement. Beginning with version 3.7.0 (2009-09-11), recursive triggers are enabled by default but can be manually disabled using PRAGMA recursive_triggers. The SQLITE_MAX_TRIGGER_DEPTH is only meaningful if recursive triggers are enabled.

The default maximum trigger recursion depth is 1000.

Maximum Depth Of Trigger Recursion