今天修复K3系统生产出库单,审核、返审核 领料数量一直不变,没有相应改变,K3是16年买的,没有源码开发文档和表结构,只能在sqlserver中添加sql语句监视,看审核后改变那个表的状态和数据源sql语句,用触发器改变数量。
提示:触发器,可以理解是自动执行的存储过程不用调用;当指定表有增删改的动作就会触发
触发器触发会有两张表,存放改动前后的数据
参考
CREATE TRIGGER [dbo].[Quantity_Update] /*Update 触发器*/
ON [dbo].ICStockBill /*目标表*/
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @FInterID INT;
DECLARE @FStatus INT;
/*FStatus 0--未审核、1--已审核、2--部分行关闭、3--已关闭*/
IF(UPDATE(FStatus))
BEGIN
SELECT @FInterID = Inserted.FInterID,
@FStatus = Inserted.FStatus
FROM Inserted;
/*SELECT *FROM Inserted ----更改后 新
SELECT *FROM Deleted ----更改前 旧*/
UPDATE Pe
SET Pe.FStockQty = CASE
WHEN @FStatus = 1 THEN (Pe.FStockQty + Ics.FQty)
WHEN @FStatus = 0 THEN (Pe.FStockQty - Ics.FQty)
END,
Pe.FAuxStockQty = CASE
WHEN @FStatus = 1 THEN (Pe.FAuxStockQty + Ics.FQty)
WHEN @FStatus = 0 THEN (Pe.FAuxStockQty - Ics.FQty)
END
FROM PPBOMEntry Pe
INNER JOIN ICStockBillEntry Ics
ON Ics.FSourceInterId = Pe.FICMOInterID
AND Ics.FItemID = Pe.FItemID
AND Ics.FInterID = @FInterID /*审核id*/
WHERE Pe.FAuxQtyMust <> 0;
/*审核记录*/
INSERT [dbo].[Statust]
SELECT @FStatus,GETDATE();
END
END;
把K3系统数据库,sql server profiler 加入监视,发现很多有两条一起执行的,如图
修改表相当于执行了两次(肯定是有一条是重复执行的,返回影响行肯定是0),那触发器也就执行了两次,更改的随之数量就会出现问题;所以需要指定改动是那个字段,前面说到是审核, FStatus 就是审核状态,加个判断条件 IF(UPDATE(FStatus)),获取有效的动作改动
IF(UPDATE(FStatus))
BEGIN
—业务逻辑
END
加入一个简单的记录,用于记录什么时候更新的
CREATE TABLE [dbo].[Statust]
(
FStatus INT NULL,
dataend DATETIME DEFAULT GETDATE() null
);
/*审核记录*/
INSERT [dbo].[Statust]
SELECT @FStatus,GETDATE();
另外触发器,有两个备份表Inserted 、Deleted
SELECT *FROM Inserted ----更改后 新
SELECT FROM Deleted ----更改前 旧/
可以做改动的数据备份
核心逻辑部分就是,获取到有效的状态改动,就需要变更相应的数量
UPDATE Pe
SET Pe.FStockQty = CASE
WHEN @FStatus = 1 THEN (Pe.FStockQty + Ics.FQty)
WHEN @FStatus = 0 THEN (Pe.FStockQty - Ics.FQty)
END,
Pe.FAuxStockQty = CASE
WHEN @FStatus = 1 THEN (Pe.FAuxStockQty + Ics.FQty)
WHEN @FStatus = 0 THEN (Pe.FAuxStockQty - Ics.FQty)
END
FROM PPBOMEntry Pe
INNER JOIN ICStockBillEntry Ics
ON Ics.FSourceInterId = Pe.FICMOInterID
AND Ics.FItemID = Pe.FItemID
AND Ics.FInterID = @FInterID /*审核id*/
WHERE Pe.FAuxQtyMust <> 0;
查看触发器
SELECT
object_name(a.parent_obj) as [表名]
,a.name as [触发器名称]
,(case when b.is_disabled=0 then '启用' else '禁用' end) as [状态]
,b.create_date as [创建日期]
,b.modify_date as [修改日期]
,c.text as [触发器语句]
FROM sysobjects a
INNER JOIN sys.triggers b
ON b.object_id=a.id
INNER JOIN syscomments c
ON c.id=a.id
WHERE a.xtype='tr' AND a.name='Quantity_Update' --触发器的名称