SQLite using NEW in subquery in a trigger-CodePudding

I'm trying to create a trigger that classifies some news in my database by category after every insert on the table news if the attribute title contains "COVID-19" or "coronavirus" (then the category id should be the one with the name "COVID-19") or if the attribute title contains "Ukraine" or "Rusia" (then the category id should be the one with the name "War").

The tables have the following structure:

news (id, title, text, date, user(FK))
category (id, name, description)
new_cat(news(FK), category(FK))

And I tried implementing the following trigger without success:

CREATE TRIGGER news_categorizer AFTER INSERT ON news
FOR EACH ROW
BEGIN
    INSERT INTO new_cat (news, category) VALUES (NEW.id, 
    (SELECT id from category WHERE name = "COVID-19" AND (NEW.title = "%COVID-19%" or NEW.title = "%coronavirus%")));
END;

How could I properly develop this subquery or can I do different INSERTs depending on a condition?

CodePudding user response：

Use properly the operator LIKE in a CASE expression:

CREATE TRIGGER news_categorizer AFTER INSERT ON news
FOR EACH ROW
BEGIN
    INSERT INTO new_cat (news, category) 
    SELECT NEW.id, 
           id 
    FROM category 
    WHERE name = CASE
      WHEN (NEW.title LIKE '%COVID-19%') OR (NEW.title LIKE '%coronavirus%') THEN 'COVID-19'
      WHEN (NEW.title LIKE '%Ukraine%') OR (NEW.title LIKE '%Rusia%') THEN 'War'
    END;
END;

See a simplified demo.