SQLite update trigger changes all rows in the table

Question

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:

[names]

id INTEGER PRIMARY KEY
name TEXT
len INTEGER

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;

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;

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;

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?

Answer 1

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.

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;