MySql触发器之入库与出库

最新推荐文章于 2023-02-11 15:13:41 发布

原创最新推荐文章于 2023-02-11 15:13:41 发布 · 3k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #structure #insert #each

Mysql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍如何使用数据库触发器实现入库和出库操作的自动化管理，通过触发器结构设计，简化业务流程，提高库存管理效率。利用触发器在数据库层面自动更新相关表中的数据，确保库存数量实时准确，减少人工操作错误。

入库

-- ----------------------------
-- Trigger structure for t_afterInsert_on_jxypcg

采购
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `t_afterInsert_on_jxypcg` AFTER INSERT ON `jw_jxypcg` FOR EACH ROW begin
declare v_2 int(10);
declare v_3 int(10);
declare v_4 int(10);
declare v_5 int(10);
set v_2=new.JXYPCG_ID;
set v_3=new.CGSL;
set v_4=(SELECT COUNT(*) FROM jw_jxypkcb AS j WHERE j.JXYPJBXX_ID=new.JXYPJBXX_ID );
IF v_4 !=0 THEN
set v_5=(SELECT SJKCS FROM jw_jxypkcb AS j WHERE j.JXYPJBXX_ID=new.JXYPJBXX_ID );
UPDATE jw_jxypkcb SET SJKCS= v_3+v_5 WHERE JXYPJBXX_ID=new.JXYPJBXX_ID;
ELSE
insert into jw_jxypkcb set JXYPJBXX_ID=new.JXYPJBXX_ID,SJKCS=v_3;
END IF;
end;;
DELIMITER ;

出库

-- ----------------------------
-- Trigger structure for t_afterInsert_on_jxyply

领用
-- ----------------------------
DELIMITER ;;
CREATE TRIGGER `t_afterInsert_on_jxyply` AFTER INSERT ON `jw_jxyplyb` FOR EACH ROW begin
declare v_3 int(10);
declare v_4 int(10);
declare v_5 int(10);
set v_3=new.LYSL;
set v_4=(SELECT COUNT(*) FROM jw_jxypkcb AS j WHERE j.JXYPJBXX_ID=new.JXYPJBXX_ID );
IF v_4 !=0 THEN
set v_5=(SELECT SJKCS FROM jw_jxypkcb AS j WHERE j.JXYPJBXX_ID=new.JXYPJBXX_ID );
UPDATE jw_jxypkcb SET SJKCS= v_5-v_3 WHERE JXYPJBXX_ID=new.JXYPJBXX_ID;
END IF;
end;;
DELIMITER ;

触发器减少了许多用程序很麻烦实现的业务，很好用。