sqlite UPDATE 触发器也会在 INSERT 语句上触发吗？

发布于 2024-09-09 09:05:07 字数 968 浏览 0 评论 0原文

我正在努力设置一个简单的 SQLite 数据库以通过 Python 访问。到目前为止，我有一个基本表和几个触发器 - 我希望有一个触发器在添加新记录时更新字段列“date_added”，另一个触发器在稍后更新记录时更新列“date_updated” 。这是我的触发器的 SQLite 语法：

CREATE TRIGGER add_contact AFTER INSERT ON contact_info  
BEGIN  
    UPDATE contact_info SET date_added = DATETIME('NOW') WHERE pkid = new.pkid;  
END;  

CREATE TRIGGER update_contact AFTER UPDATE ON contact_info  
BEGIN  
    UPDATE contact_info SET date_updated = DATETIME('NOW') WHERE pkid = new.pkid;  
END;

“add_contact”触发器似乎工作正常...当我按计划通过 sql INSERT 命令添加新记录时，它会触发。

问题似乎是“update_contact”触发器...当我通过 sql UPDATE 命令更新记录时（按计划）和当我添加新记录时它也会触发：

即当我添加时我在“date_added”和“date_updated”列中得到了一条新记录：

2010-07-12 05:00:06|2010-07-12 05:00:06

当我更新该记录时，它会发生如下变化：

2010-07-12 05:00:06|2010-07-12 05:14:26

我想我不明白为什么 UPDATE 触发器也会在 INSERT 上触发？

TIA，

Monte

编辑补充：有关如何使其按预期工作的任何提示？

原文

I'm working on setting up a simple SQLite database to access via Python. So far I have one basic table, and a couple of triggers - I want to have one trigger update a field column 'date_added' when a new record is added, and another one to update a column 'date_updated' when a record is later updated. Here is my SQLite syntax for the triggers:

CREATE TRIGGER add_contact AFTER INSERT ON contact_info  
BEGIN  
    UPDATE contact_info SET date_added = DATETIME('NOW') WHERE pkid = new.pkid;  
END;  

CREATE TRIGGER update_contact AFTER UPDATE ON contact_info  
BEGIN  
    UPDATE contact_info SET date_updated = DATETIME('NOW') WHERE pkid = new.pkid;  
END;

The 'add_contact' trigger seems to be working fine... it fires when I add a new record via an sql INSERT command, as planned.

The problem seems to be the 'update_contact' trigger... it fires both when I update a record via an sql UPDATE command (as planned) and when I add a new record also:

i.e. when I add a new record I get this in the 'date_added' and 'date_updated' columns: