Eric Eric - 4 months ago 31
SQL Question

SQLite trigger Update Of same row without knowing id?

How can I get something like this to work in SQLite? I want it to only apply to the row that got updated, given that many other rows may share the same first name.

CREATE TRIGGER update_directory_fName UPDATE OF fName ON directory
BEGIN
UPDATE directory SET wholeName = (new.fName || lName) WHERE old.id = new.id;
END;


I'm know that the id isn't being updated but I can't figure out how to make it know which row I'm talking about. I've tried just referencing id to new.id as well but it doesn't help. The result when I change fName is that wholeName doesn't change at all. It doesn't throw any errors either so I'm thinking that it doesn't know what to update?

Thanks for any help!

Answer

I believe you were adding an alias in the where Id field that doesn't exist, so:


CREATE TRIGGER update_directory_fName UPDATE OF fName ON directory 
  BEGIN
    UPDATE directory SET wholeName = (new.fName || lName) WHERE Id = new.id;
  END;