更新后的SQLite触发器

Question

我的表有时间戳列。我想要一个触发器，当更新行且未在更新语句中指定时间戳时，该触发器在受影响的行上将时间戳设置为 0。

如果我使用此触发器：

CREATE TRIGGER AFTER UPDATE ON mytable FOR EACH ROW
WHEN (NEW.timestamp IS NULL)
BEGIN
UPDATE mytable SET timestamp = 0 WHERE id = NEW.id;
END;

然后，触发器不会为此更新语句触发：

UPDATE mytable SET comecolumn='some'

即受影响行的时间戳不会更改为 0。

你能帮我定义触发器吗？

Answer 1

对 UPDATE触发器中的行进行其他更改的唯一方法是之后对同一表执行另一个 UPDATE。

检测列值是否更改的唯一方法是比较旧行值和新行值;触发器不知道原始 UPDATE 语句中实际提到了哪些列。

为了防止触发器以递归方式触发自身，应将其限制为由除时间戳之外的所有列的更改触发：

CREATE TRIGGER clear_timestamp
AFTER UPDATE OF all_the, other, columns ON MyTable
FOR EACH ROW
WHEN OLD.timestamp = NEW.timestamp
BEGIN
UPDATE MyTable
SET timestamp = 0
WHERE id = NEW.id;
END;

Answer 2

我认为问题是在SET语句中扩展到每一列，每一列都设置为数据库中的当前值。因此，如果当前timestamp列NULL，则原始触发器仅有效。

解决方案可能是创建另一个触发器，将时间戳列重置为UPDATE之前的NULL。

CREATE TRIGGER "set_null"
BEFORE UPDATE ON "mytable" FOR EACH ROW 
BEGIN
UPDATE mytable set timestamp = NULL where rowid = NEW.rowid;
END

这样，如果未在UPDATE SET中指定NEW.timestamp，则会NULL。

显然，现在无法对时间戳设置NOT NULL约束。

另一个问题是，执行更新查询时，触发器递归必须关闭：

PRAGMA recursive_triggers = OFF;

Answer 3

这是另一种方式：

import sqlite3
conn = sqlite3.connect(':memory:')
c = conn.cursor()
name = {'name':'jack'}
c.execute("""CREATE TABLE Programs (
id INTEGER PRIMARY KEY,
name VARCHAR(64) NOT NULL,
time_added INTEGER
);""")
c.execute("""CREATE TRIGGER program_time_added AFTER INSERT ON Programs
FOR EACH ROW
BEGIN
UPDATE Programs SET time_added =datetime('now', 'localtime') WHERE id = NEW.id;
END;""")
c.execute('INSERT INTO Programs (name) VALUES (?)', [name['name']])