SQLite更新触发器更改表中的所有行 [英] SQLite update trigger changes all rows in the table

问题描述

问题：最简单的更新触发器将一个新值写入所有表行，而不仅仅是被更新的行。表格如下：

Problem: a simplest possible update trigger writes a new value to all table rows instead of just the row being updated. Here is the table:

[名称]

id INTEGER PRIMARY KEY
name TEXT
len INTEGER

现在我想创建触发器以使用名称的长度更新 len。这个INSERT触发器似乎在做这项工作：

Now I want to create triggers to update 'len' with the length of 'name'. This INSERT trigger seems to be doing the job corectly:

CREATE TRIGGER 'namelen' AFTER INSERT ON 'names'
BEGIN
UPDATE 'names' SET len = length(NEW.name) WHERE (id=NEW.id);
END;

当我添加类似的UPDATE触发器时问题就开始了：

Problems begin when I add a similar UPDATE trigger:

CREATE TRIGGER 'namelenupd' AFTER UPDATE ON 'names'
BEGIN
UPDATE 'names' SET len = length(NEW.name) WHERE (OLD.id=NEW.id);
END;

更新触发器将新长度写入表的所有行，尽管有WHERE子句。例如，如果我说

The update trigger writes the new length to all rows of the table, despite the WHERE clause. For example, if I say

UPDATE 'names' SET name='foo' where id=1;

然后，对于所有行， len的值变为3表。我查看了sqlite触发器示例，但看不到我的错误。我还必须做些什么以确保触发器仅更新实际更新的行中的 len列 仅？

then the value of 'len' becomes 3 for all rows of the table. I've looked at sqlite trigger examples and I can't see my error. What else must I do to make sure the trigger updates the 'len' column only in the row(s) that are actually updated?

推荐答案

OLD.xxx和NEW.xxx都引用导致触发器运行的表行。
触发器内的UPDATE语句独立运行；如果要将其限制为一个表行，则必须在其WHERE子句中通过过滤 that 语句的表值（即 names.id 或只是 id 。

Both OLD.xxx and NEW.xxx refer to the table row that caused the trigger to run. The UPDATE statement inside the trigger runs independently; if you want to restrict it to one table row, you have to explicitly do this in its WHERE clause by filtering on that statement's table values, i.e., names.id or just id.

当原始UPDATE语句不更改 id 列，旧的和新的 id 值相同，表达式 OLD.id = NEW.id 对于表中的 all 记录都是正确的，如内部UPDATE语句所示。

When the original UPDATE statement does not change the id column, the old and new id values are the same, and the expression OLD.id=NEW.id is true for all records in the table, as seen by the inner UPDATE statement.

正确的触发器看上去像这样：

The correct trigger looks like this:

CREATE TRIGGER "namelenupd"
AFTER UPDATE OF name ON "names"
BEGIN
    UPDATE "names" SET len = length(NEW.name) WHERE id = NEW.id;
END;

这篇关于SQLite更新触发器更改表中的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！