MySql触发器之入库与出库

入库

-- ----------------------------
-- 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 ;

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