mysql - 插入 MySQL 触发器后更新值或在另一个表中插入新行

Question

我有两张 table ，

RawFeed , SellerList.

当新行插入RawFeed时，我需要在 SellerList 中插入一行如果对应SellerID SellerList 中不存在,

否则，我需要增加 SellerList 中的两列值表SellerID 。

这是我的触发代码-

CREATE TRIGGER `SellerListUpdate` AFTER INSERT ON `RawFeed`
FOR EACH ROW
    BEGIN
        --CHECK SELLER ID EXISTS OR NOT.
        DECLARE SELLEREXISTS INT;
        SELECT COUNT(*) INTO SELLEREXISTS FROM SellerList WHERE SellerList.SellerID=NEW.seller_id;

        IF SELLEREXISTS=0 THEN
            --INSERT ROW
            INSERT INTO SellerList(`SellerID`, `Total`, `Active`) VALUES(NEW.seller_id, 1, 1);
        ELSE
            --UPDATE ROW
            UPDATE SellerList SET Total=Total+1, Active=Active+1 WHERE SellerList.SellerID=NEW.seller_id;
        END IF;
    END

我收到了这个错误-

SQL Error(1064): You have an error in your SQL Syntax;

Answer 1

尝试更改分隔符

DELIMITER $$
CREATE TRIGGER `SellerListUpdate` AFTER INSERT ON `RawFeed`
FOR EACH ROW
    BEGIN
        --CHECK SELLER ID EXISTS OR NOT.
        DECLARE SELLEREXISTS INT;
        SELECT COUNT(*) INTO SELLEREXISTS FROM SellerList WHERE SellerList.SellerID=NEW.seller_id;

        IF SELLEREXISTS=0 THEN
            --INSERT ROW
            INSERT INTO SellerList(`SellerID`, `Total`, `Active`) VALUES(NEW.seller_id, 1, 1);
        ELSE
            --UPDATE ROW
            UPDATE SellerList SET Total=Total+1, Active=Active+1 WHERE SellerList.SellerID=NEW.seller_id;
        END IF;
    END
$$
DELIMITER ;