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;

Answer 2

Had the same issue, here's the syntax from my trigger

You would change "ALTER" to "CREATE" depending on what you already have (or not)

You have "id" as your primary key

Your dbo is "names"

Obviously, this will set the name value to "foo" (not really what you wanted). The key seems to be the last line, where you set inner join inserted on names.Id = inserted.Id.

USE [yourDBname]
    ALTER TRIGGER [dbo].[yourTrigger]
       ON  [dbo].[names] 
       After INSERT, UPDATE 
    AS 

    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

    Select id from inserted
        begin
         update [dbo].names
            set [dbo].names.name = 'foo' 
            from dbo.names 
               inner join inserted 
               on names.id = inserted.id
        END