SQLITE在触发器中使用新的子查询

发布于 2025-01-22 21:45:08 字数 713 浏览 0 评论 0原文

我正在尝试创建一个触发器，该触发器在我的数据库中通过类别在表上的每个插入 exter 中对某些news进行分类。 > title 包含“ covid-19”或“冠状病毒”（然后，类别ID应该是名称为“ covid-ben”），或者如果属性title> title包含“乌克兰”或“ Rusia”（然后类别ID应该是名称为“战争”的类别）。

这些表具有以下结构：

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

我尝试实现以下触发器而没有成功：

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;

我如何正确地开发此子查询，或者我可以根据条件进行不同的插入件？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凯凯我们等你回来 2025-01-29 21:45:08

在案例表达式中正确使用操作员 fike 表达式：

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;

请参阅简化的 demo 。

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.

回复收藏 0 原文

~没有更多了~