我有一个带有名为“NOTES”的STRING类型列的sqlite表.我想创建一个自动更新NOTES列内容的触发器,但不会完全替换它.
以下不起作用,我没有看到错误,NOTES列永远不会更新.
CREATE TRIGGER AlterNote
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN UPDATE MyTable
SET NOTES= NOTES || 'DATEMODIFIED: ' || date('now')
WHERE rowid=NEW.rowid;
END;
这确实有效,因为我不再引用我正在更新的内容：
CREATE TRIGGER AlterNote
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN UPDATE MyTable
SET NOTES= 'DATEMODIFIED: ' || date('now')
WHERE rowid=NEW.rowid;
END;
有没有办法做到这一点？基本上注意=注意“等等”
解决方法
使用new.COLUMN_NAME语法.
CREATE TRIGGER AlterNote
AFTER INSERT ON MyTable
FOR EACH ROW
BEGIN UPDATE MyTable
SET NOTES= new.Notes || ',DATEMODIFIED: ' || date('now')
WHERE rowid=NEW.rowid;
END;
参见示例运行.
sqlite> sqlite> create table mytable(Notes text); sqlite> insert into mytable(Notes) Values('aa'); sqlite> select * from mytable; aa sqlite> CREATE TRIGGER AlterNote ...> AFTER INSERT ON MyTable ...> FOR EACH ROW ...> BEGIN UPDATE MyTable ...> SET NOTES= new.Notes || ',DATEMODIFIED: ' || date('now') ...> WHERE rowid=NEW.rowid; ...> END; sqlite> sqlite> sqlite> sqlite> sqlite> insert into mytable(Notes) Values('aa2'); sqlite> select * from mytable; aa aa2,DATEMODIFIED: 2014-12-22
