Eric Eric - 1 year ago 78
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 Source

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;