MySQL 触发 if-else 插入更新

Question

我试图让这段代码工作但无济于事..

DELIMITER $$

CREATE
    TRIGGER `update_tbl1` AFTER UPDATE 
    ON `tbl1` 
    FOR EACH ROW BEGIN

        IF (SELECT count(*) FROM tbl1 WHERE stn=NEW.stn) = 1
        THEN
        UPDATE tbl2 SET date_posted=NEW.date_posted WHERE stn=NEW.stn;
        ELSE
        INSERT INTO tbl2 (stn) VALUES (NEW.stn);
        END IF

    END$$

DELIMITER ;

我有两个表，我想要一个触发器，它会在 tbl1 更新时更新 tbl2，前提是数据已经存在于 tbl2 上，否则，插入它。我的代码似乎可行，错误似乎与语法有关，但我找不到位置。

编辑:

这里是错误:

 #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 'INTO tbl2 (stn) VALUES (NEW.stn); END IF END' at line 10

Answer 1

关于语法错误，我认为这只是你在 END IF 之后忘记了分号 ;，请尝试以下操作:

DELIMITER $$

CREATE
    TRIGGER `update_tbl1` AFTER UPDATE 
    ON `tbl1` 
    FOR EACH ROW BEGIN

        IF (SELECT count(*) FROM tbl1 WHERE stn=NEW.stn) = 1
        THEN
        UPDATE tbl2 SET date_posted=NEW.date_posted WHERE stn=NEW.stn;
        ELSE
        INSERT INTO tbl2 (stn) VALUES (NEW.stn);
        END IF;

    END$$

DELIMITER ;