用MySQL触发语法错误

Question

我想在MySQL中创建一个触发器，但是我得到了一个语法错误。我尝试使用PHPMyAdmin或直接从MySQL客户端运行查询。

以下是错误：

1064 -您的SQL语法有一个错误；请检查与您的MySQL服务器版本对应的手册，以获得在第5行使用near‘的正确语法。

CREATE TRIGGER asterisk.bi_queueEvents BEFORE INSERT ON aasterisk.queue_log
FOR EACH ROW
BEGIN
IF (NEW.event = 'ADDMEMBER')THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSIF (NEW.event = 'REMOVEMEMBER')THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'LOGGEDOUT',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSIF (NEW.event = 'PAUSE')THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'PAUSE',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
ELSIF (NEW.event = 'UNPAUSE')THEN
INSERT INTO agent_status (agentId,agentStatus,timestamp,callid) VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
END IF;
END ;
//

DELIMITER ;

Answer 1

我找到了解决方案，这是一个好的要求：

delimiter |
CREATE TRIGGER bi_queueEvents
BEFORE INSERT ON queue_log
FOR EACH ROW
BEGIN
    IF NEW.event = 'ADDMEMBER' THEN
        INSERT INTO agent_status (agentId,agentStatus,timestamp,callid)
        VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
    ELSEIF NEW.event = 'REMOVEMEMBER' THEN
        INSERT INTO agent_status (agentId,agentStatus,timestamp,callid)
        VALUES (NEW.agent,'LOGGEDOUT',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "LOGGEDOUT", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
    ELSEIF NEW.event = 'PAUSE' THEN
        INSERT INTO agent_status (agentId,agentStatus,timestamp,callid)
        VALUES (NEW.agent,'PAUSE',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "PAUSE", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
    ELSEIF NEW.event = 'UNPAUSE' THEN
        INSERT INTO agent_status (agentId,agentStatus,timestamp,callid)
        VALUES (NEW.agent,'READY',FROM_UNIXTIME(NEW.time),NULL) ON DUPLICATE KEY UPDATE agentStatus = "READY", timestamp = FROM_UNIXTIME(NEW.time), callid = NULL;
    END IF;
END |
delimiter ;

Answer 2

你试过ELSIF而不是ELSEIF吗？

IF(boolean_expression 1)THEN 
   S1; -- Executes when the boolean expression 1 is true 
ELSIF( boolean_expression 2) THEN
   S2;  -- Executes when the boolean expression 2 is true