用于条件触发器的 Django 迁移 sql
我想创建一个仅在满足条件时才插入到表中的触发器.我尝试过使用 IF/BEGIN/END 和 WHERE 的各种组合,但 Django 每次都返回一个 SQL 语法错误.
I want to create a trigger that only inserts into table when the condition is met. I've tried using various combinations of IF/BEGIN/END and WHERE, but Django returns me an SQL syntax error each time.
这里type_user_id指的是触发点赞的人,user_id指的是收到通知的人.它应该只在他们不是同一个人时触发.
Here, type_user_id refers to the person who triggers the like, and user_id refers to the person who receives the notification. It should only trigger when they are not the same person.
operations = [
migrations.RunSQL(
"DROP TRIGGER like_notification",
"""
CREATE TRIGGER like_notification AFTER INSERT ON user_likes_article
FOR EACH ROW
IF (select user_id from channel WHERE channel_id IN
(SELECT channel_id FROM article WHERE article_id = NEW.article_id)) <> NEW.user_id
BEGIN
INSERT INTO notification (type, type_id, article_id, is_read, type_user_id, user_id)
VALUES ('like', NEW.article_id, NEW.article_id, false, NEW.user_id,
(SELECT user_id from channel WHERE channel_id IN
(SELECT channel_id FROM article WHERE article_id = NEW.article_id)
)
)
END;
"""
)
]
>>>django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for the right syntax
to use near 'BEGIN
INSERT INTO notification (type, type_id, article_id, is_re' at line 5")
和
CREATE TRIGGER like_notification AFTER INSERT ON user_likes_article
FOR EACH ROW
BEGIN
DECLARE @user_same Boolean;
SELECT 1 into @user_same
FROM channel
WHERE channel_id IN (select channel_id FROM article where article_id = NEW.article_id)
AND user_id = NEW.user_id;
IF @user_same <> 1
THEN
INSERT INTO notification (type, type_id, article_id, is_read, type_user_id, user_id,)
VALUES ('like', NEW.article_id, NEW.article_id, false, NEW.user_id, (
SELECT user_id from channel WHERE channel_id IN (
SELECT channel_id FROM article WHERE article_id = NEW.article_id)
)
END IF;
END;
>>>You have an error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near '@user_same Boolean;
SELECT 1 into @user_same
FRO' at line 4"
和
CREATE TRIGGER like_notification AFTER INSERT ON user_likes_article
FOR EACH ROW
INSERT INTO notification (type, type_id, article_id, is_read, type_user_iduser_id)
VALUES ('like', NEW.article_id, NEW.article_id, false, NEW.user_id,
(
SELECT user_id from channel WHERE channel_id IN
(SELECT channel_id FROM article WHERE article_id = NEW.article_id)
)
WHERE NOT user_id = NEW.user_id;
>>>You have an error in your SQL syntax; check the manual that corresponds to your
MariaDB server version for the right syntax to use near 'WHERE NOT user
我真的不明白为什么它们都不起作用.任何帮助将非常感激!
I don't really understand why none of them work. Any help would be much appreciated!
推荐答案
另一种简单的方法.
您应该在 ;
分隔符之间添加任何符号,以便将 MySQL 语法适当地转换为原始 SQL.
You should add any symbols between ;
delimiters for the appropriate converting MySQL syntax to the raw SQL.
CREATE TRIGGER trigger_name BEFORE INSERT ON table
FOR EACH ROW
BEGIN
IF NEW.number <> 'anynumber' AND NEW.number <> 'anynumber'
THEN
SET NEW.number = 'anynumber'; --
END IF; --
END
由于破折号符号,此示例有效.
This example works due to the dash symbols.
相关文章