Hello my table looks like this:
CREATE TABLE my_eav( id INTEGER PRIMARY KEY AUTOINCREMENT, k TEXT NOT NULL, v TEXT, v_link INTEGER, UNIQUE(k, v) ON CONFLICT REPLACE, FOREIGN KEY(v_link) REFERENCES posts(id) ON UPDATE ??? );
v_link is the id column of another table, "posts". When it's not null, the "v" column should take the value of the "title" column from "posts".
When the referenced post record is updated, I'd like to change the "v" column on this table to the "title" column of the posts table. Is that possible?
... ON UPDATE SET v = posts.title
but of course this doesn't work :(
I made this trigger:
CREATE TRIGGER my_trigger AFTER INSERT ON my_eav WHEN v_link IS NOT NULL BEGIN UPDATE my_eav SET v = (SELECT title FROM posts WHERE id = v_link); END;
But I don't know if it's correct