Mysql ON DUPLICATE KEY UPDATE inside Triger 语法错误

Question

谢谢鲑鱼。我也有同样的情况并工作。这是我第一次使用 MySql 的触发器。我已经在 MS Sql 服务器上使用触发器多年了，我发现它在 Mysql 中很奇怪。它似乎永远不会触发或者它捕获事件的速度不够快。让我写一下真正的代码并解释一下。

DROP trigger if exists sales_ins;
delimiter //
CREATE TRIGGER sales_ins
AFTER INSERT ON pos_items
FOR EACH ROW
BEGIN
DECLARE new_am INTEGER;
SET new_am = 0;
If NEW.item_class = 0 Then
    Select
          Sum(ifnull(pos_items.pos_quantity,0)) INTO new_am
        From
             pos_items Right Join pos_invoice On pos_items.inv_number = pos_invoice.inv_number And
            pos_items.branch_id = pos_invoice.branch_id
        Where
            pos_invoice.inv_void_cacel = 0 And pos_items.item_class = 0 And pos_items.item_code = NEW.item_code
        Group By
            pos_items.item_code
        Having
            Not pos_items.item_code Is Null;
    REPLACE INTO item_quantity VALUES (NEW.company_id, NEW.branch_id, NEW.item_code, 
        0, 0, 0,    new_am, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, now(), NEW.rowguid);
ElseIf NEW.item_class = 7 Then
    Select
          Sum(ifnull(pos_items.pos_quantity,0)) INTO new_am
        From
             pos_items Right Join pos_invoice On pos_items.inv_number = pos_invoice.inv_number And
            pos_items.branch_id = pos_invoice.branch_id
        Where
            pos_invoice.inv_void_cacel = 0 And pos_items.item_class = 7 And pos_items.item_code = NEW.item_code
        Group By
            pos_items.item_code
        Having
            Not pos_items.item_code Is Null;
    REPLACE INTO item_quantity VALUES (NEW.company_id, NEW.branch_id, NEW.item_code, 
        0, 0, 0, 0, new_am, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, now(), NEW.rowguid);
End If;
END //
delimiter ;

问题是，它没有更新。为了清楚起见，我将解释代码。一旦 pos_items 表在插入后触发(使用插入后，因为我也需要将此数量求和)，它将把所有交易总和作为销售数量并更新 item_quantity item_sold_qty 字段，或者如果它是返回，则更新 item_salesret_qty 字段。 p>

没有发生的是更新的数量不准确。错误是如何以及在哪里的，或者是像每个创建 View 都会创建一个myisam表一样，这也是延迟执行。只是我不明白。感谢您的帮助。

Answer 1

DROP TRIGGER IF EXISTS sales_ins;
delimiter //
CREATE TRIGGER sales_ins
AFTER INSERT ON pos_items
FOR EACH ROW
BEGIN
INSERT INTO item_quantity (
    company_id,branch_id,item_Code,item_Pur_Qty,item_PurRet_Qty,item_Damaged,
    item_Sold_Qty,item_SalesRet_Qty,item_OtherSales_Ret,item_Stock_From,
    item_Stock_To,item_Stock_Adjust,rem_Sales,item_Transfered,item_Stores_Qty,
    item_MarRets,item_DelPur,item_Samples,item_Tailor,dateguid,rowguid)
VALUES (
    NEW.company_id, NEW.branch_id, NEW.item_code, 0, 0, 0,
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, now(), NEW.rowguid)
ON DUPLICATE KEY UPDATE
    item_Sold_Qty = 0, 
    dateguid = now();
END //
delimiter ;