SQLite 更新触发器更改表中的所有行

2022-01-01 00:00:00 sqlite triggers

问题:最简单的更新触发器将新值写入所有表行，而不仅仅是被更新的行.这是表:

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

现在我想创建触发器来更新长度为 'name' 的 '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 语句独立运行；如果您想将其限制为一个表行，您必须通过过滤 that 语句的表值，即 names.id 或只是在其 WHERE 子句中明确执行此操作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 对表中的所有 记录为真，如内部 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;

相关文章