在mysql中触发导致错误 [英] Trigger in mysql causing error

问题描述

以下代码中的错误是什么.我正在mysql中执行

What is the error in the following code. I am executing in mysql

CREATE TRIGGER tg_order_insert
BEFORE INSERT 
    ON `order` FOR EACH ROW
BEGIN
    INSERT INTO `grocery`.`order_seqid` VALUE(NULL);
    SET NEW.order_id = CONCAT('#GNC', LPAD(LAST_INSERT_ID(),3,'0'));
END;

杂货是数据库，order_seqid和order是2表. 如果类型为int且自动递增，order_seqid是仅具有1个属性的表.
我正在尝试在要插入订单表的ID上添加前缀. 我在INSERT INTO.....和END;行中遇到2个错误

Grocery is the database and order_seqid and order are 2 table. order_seqid is a table with only 1 attribute if type int and auto increment.
Am trying to put a prefix on the id which we insert into order table. I am getting 2 errors in INSERT INTO..... and END; line

推荐答案

您是否在触发器定义之前声明了定界符?像

Did you declare a delimiter before your trigger definition? Something like

DELIMITER //
CREATE TRIGGER tg_order_insert
BEFORE INSERT 
    ON `order` FOR EACH ROW
BEGIN
    INSERT INTO `grocery`.`order_seqid` VALUE(NULL);
    SET NEW.order_id = CONCAT('#GNC', LPAD(LAST_INSERT_ID(),3,'0'));
END
//

因为不这样做，那么MySQL会在看到第一个;并调用语法错误时认为您试图结束触发器定义.

Because if you don't, then MySQL thinks you're trying to end your trigger definition when it sees that first ; and calls syntax error.

这篇关于在mysql中触发导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！