mysql - 为什么 MySQL 在创建触发器时出现此错误？

Question

我创建了这个触发器来自动生成用户代码，例如 SDA0001、SDA0002...我刚刚包含了 BEGIN 和 END 之间的主要语句。它是在表agent_mst

上 BEFORE INSERT创建的

DECLARE max_id INT;

SET max_id=(SELECT MAX(agent_id_pk)+1 FROM `agent_mst`);
IF (max_id IS NULL) THEN
    SET max_id=1;
END IF;

SET 
NEW.date_added=NOW(), 
NEW.date_updated=NOW(),
NEW.agent_code = CONCAT('SDA', LPAD(max_id, 4,'0'));

它给出的错误如下

MySQL said: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE max_id INT; SET max_id=(SELECT MAX(agent_id_pk)+1 FROM agent_mst); ' at line 1

我正在使用 phpMyAdmin 创建此触发器。

有什么建议吗？提前致谢。

Answer 1

这应该有效:

DELIMITER $$

CREATE TRIGGER trigger_name BEFORE INSERT ON `agent_mst`
FOR EACH ROW 
BEGIN
    DECLARE max_id INT;

    SET max_id=(SELECT MAX(agent_id_pk)+1 FROM `agent_mst`);
    IF (max_id IS NULL) THEN
        SET max_id=1;
    END IF;

    SET NEW.date_added=NOW(),
        NEW.date_updated=NOW(),
        NEW.agent_code = CONCAT('SDA', LPAD(max_id, 4,'0'));

END $$

DELIMITER ;