用于条件触发器的Django迁移SQL [英] Django migration sql for conditional triggers

问题描述

我想创建一个仅在满足条件时才插入表中的触发器. 我尝试使用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\n 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;\n                SELECT 1 into @user_same\n                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.

这篇关于用于条件触发器的Django迁移SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！