首页 文章

MySQL的触发语法错误

提问于
浏览 次
0

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

这是错误：

1064 - 您的SQL语法出错;检查与MySQL服务器版本对应的手册，以便在第5行的''附近使用正确的语法

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 ;
mysql triggers

2 回答

  • 0

    您是否尝试过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
    回复于
  • 0

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

    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 ;
    回复于

相关问题