MYSQL SELECT INSERT 触发器内

Question

    delimiter //
CREATE TRIGGER trig AFTER INSERT ON table0 FOR EACH ROW 
BEGIN
    CREATE TEMPORARY TABLE temp (SELECT t1.col1,t1.Col2,t2.col3,t3.col4,t4.col5 FROM (table1 AS t1 JOIN table2 AS t2 ON t1.col1 = t2.col3... etc) WHERE t1.condition = NEW.condition);
    INSERT INTO table5 VALUES (NULL,CURRENT_TIMESTAMP(),0,0,NULL,NULL,NULL,NEW.condition,   SELECT GROUP_CONCAT("\"",someColumn - 1,",",50,",",someColumn2,",",30,",",someColumn3,"\"" SEPARATOR ';') FROM temp);
    DROP temp;
END;//
delimiter ;

现在错误

错误 1064 (42000):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在 'SELECT CONCAT("\"",someColumn - 1,",",50,",",someColumn2,",",30,", 附近使用的正确语法",someColu' 在第 4 行

它在 someColumn3 的 someColu 处被切断

感谢您的宝贵时间。

Answer 1

您似乎错过了行分隔符。您需要添加它们。

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SET NEW.amount = 0;
    ELSEIF NEW.amount > 100 THEN
        SET NEW.amount = 100;
    END IF;
END;//
delimiter ;

必须将分隔符更改为“;”以外的其他内容以便执行不会在创建触发器语句的中间终止。

在这里，我们在创建触发器之前将分隔符更改为“//”，然后将其更改回“;”立即。