个人博客1

利用触发器实现调拨记录新增时，库存记录表中对应变化

点击查看代码

BEGIN
  -- 源仓库减库存
  UPDATE inventory 
  SET number = number - 1 
  WHERE part_name = NEW.part_name 
    AND location_name = NEW.from_location_name 
    AND status = '在库';

  -- 目标仓库增库存（存在则更新）
  UPDATE inventory 
  SET number = number + 1 
  WHERE part_name = NEW.part_name 
    AND location_name = NEW.to_location_name 
    AND status = '在库';

  -- 目标仓库无记录时插入
  IF ROW_COUNT() = 0 THEN
    INSERT INTO inventory 
      (part_name, location_name, status, number)
    VALUES
      (NEW.part_name, NEW.to_location_name, '在库', 1);
  END IF;
END