我有两张桌子

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;

最佳答案

尝试更改定界符

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 ;

关于mysql - 在插入MySQL触发器之后以更新值或在另一个表中插入新行,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/41920781/

10-12 12:33
查看更多