mercredi 22 avril 2015

Change value of column to a value from a foreign column

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?

Something like

... 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

Aucun commentaire:

Enregistrer un commentaire