mysql - 在 TRIGGER 中使用 DELETE LIMIT

Question

如何DELETE INTO .... ORDER BY ... LIMTI在触发器内部使用？这是我尝试的

BEGIN
    IF NEW.qty > 0 THEN
        SET @QTY = 0;
        REPEAT
            INSERT stock_fifo(id, sku_id, slot_id, stock_at)
            VALUES(NEW.id, NEW.sku_id, NEW.slot_id, NEW.stock_at);
            SET @QTY = @QTY + 1;
        UNTIL @QTY > NEW.qty END REPEAT;
    ELSEIF NEW.qty < 0 THEN
        DELETE FROM stock_fifo
        WHERE slot_id = NEW.slot_id AND sku_id = NEW.sku_id
        ORDER BY id
        LIMIT -NEW.qty;
    END IF;
END

它说周围有错误

LIMIT -NEW.qty

标签： mysql

Answer 1

LIMIT expressions have to be "nonnegative integer constants" (see the manual) so LIMIT -NEW.qty is a syntax error. You can loop the DELETE the same way as you loop the INSERT:

SET @QTY = 0;
REPEAT
    DELETE FROM stock_fifo
    WHERE slot_id = NEW.slot_id AND sku_id = NEW.sku_id
    ORDER BY id
    LIMIT 1;
    SET @QTY = @QTY - 1;
UNTIL @QTY < NEW.qty END REPEAT;