触发器 - 触发器的原理及常用存储过程

2020-02-15 简单的记录一下
2020-04-24 零点整：之前写的sql中大量使用了函数，并且每一行物料判断完成就要写入一次MTO的表并且调用一次校对库存的存储过程。当一张单据数据量超过100行的时候，占用太多资源，审核时间达5-6分种之久。经过这几天晚上的研究，重新调整思路，使用表连接，然后通过临时表上的计算，一次算出结果，然后一次插入MTO表，使得程序运行速度大大加快。今天出来了初步的结果，明天争取完善。
2020-04-25 今天把功能进行了完善，在系统中进行测试：采购申请单上面有多达180行物料，点击审核后仅用了3秒即完成了库存的判断，并生成了MTO调整单。而如果用原来的计算方法则很大概率无法审核完成，而且日志文件瞬间爆发至2G以上。

这里说一下，为什么开始设计的那个触发器会出现速度慢的问题。
究其主要原因，第一次设计的是把采购申请单的记录拿出来逐行去判断库存，每判断完一行记录则插入MTO调整单，而插入一条MTO调整单的记录，则需要进行一次库存的校对，所以导致占用资源高，耗时长。
新的方法使用了表联结，一次计算出结果。
⚙

一、触发器的一些基础

触发器的概念

当在表中插入、更新、删除记录时，触发一个或一系列的SQL语句

触发器类型

1.After触发器
当数据表执行插入、更新、删除操作之后，After触发器才被触发。After触发器只能用于数据表，不可用于视图。
分为：

• after insert触发器
• after update触发器
• after delete触发器
• for insert触发器
• for update触发器
• for delete触发器

2.Instead Of 触发器
当数据表执行插入、更新、删除操作，Instead Of触发器并不执行插入、更新、删除操作，而是替换成执行触发器本身的T-SQL逻辑。
分为：

• instead of insert触发器
• instead of update触发器
• instead of delete触发器

Instead Of触发器也可用于视图，用来扩展是多表连接的视图设计可以进行更新操作。视图设计是多表连接，是无法更新的。这时候，可以用Instead Of触发器，把更新操作替换为向每个基本表进行更新。

触发器的2个中间表

触发器触发的时候有两个中间表：插入表（instered表）和删除表（deleted表）。这两张表的字段，和触发器应用主表的字段相同。中间表存储的数据是执行插入、更新、删除操作的数据，当触发器执行完成后，这两张表就会被删除。Inserted表的数据是待插入的数据，或是触发器修改后的数据。deleted表的数据是插入前的原始数据。如果是删除操作的触发器，deleted表是表示删除的数据。

触发器的语法结构

CREATE TRIGGER trigger_name
ON {Table_name|View_name}
{FOR|AFTER|INSTEAD OF}  [INSERT,UPDATE,DELETE]
AS
  声明部分
BEGIN
  执行部分
END

删除触发器

DROP TRIGGER trigger_name

查询数据库中已有的触发器

SELECT * FROM sysobjects where xtype = 'TR'

查看某个触发器的具体内容

EXEC sp_helptext 'trigger_name'

触发器的启用和停用

alter table tablename disable trigger trigname
alter table tablename enable trigger trigname

sql profiler如何捕捉触发器

sqlserver profiler 的event里选择这两个：
Stored procedures: - SP:StmtStarting - SP:StmtCompleted

二、初步测试

初步在采购申请单上创建一个触发器, 在单据审核时修改单据的编码

create trigger tr_POrequest_MTOchange
on POrequest
after update
as
begin
declare @billno nvarchar(100)
    if UPDATE(FCheckerID) begin        
        select @billno = fbillno from deleted
        update PORequest set FBillNo = 'rp001'
            where FBillNo = @billno
    end
end
-- 这是一个字段级的触发器, 为了能识别单据是保存时执行了update还是审核时执行了update, 
-- 所以使用了if update(字段); 当FCheckerID字段改变后, 才会触发后面的SQL语句

三、SQL相关知识

SQL表循环

需要在采购申请明细表中遍历所有行的物料, 通过循环的方法实现. (通过游标应该也可以)
参考: https://www.cnblogs.com/yssss/p/10731626.html

set nocount on
--创建一个表变量, 用来存储要遍历的内容
declare @t_MTOEntry table (FDetailID int)
        insert into @t_MTOEntry from select ... --插入表内容, 省略(这里放的是采购申请的FDetailID)
select ROW_NUMBER() over(order by FDetailID) as i,* into #t_id from @t_MTOEntry --创建临时表,加上序号

declare @count int 
declare @i int = 1
select @count = COUNT(1) from #t_id   --获取临时表的记录数,语法:/SQL/64.html
while (@i<=@count)
    begin
        declare @FDetailID int
        select @FDetailID=FDetailID from #t_id where i = @i
        print str(@FDetailID)
        set @i = @i+1
    end    

drop table #t_id

SQL函数

SQL自定义函数 : https://www.cnblogs.com/happyyangyanghappy/p/10870495.html

SQL自定义函数主要分为2种 :

• 标量函数:
  返回单个数据
• 表格值函数:
  一种是函数中只有一条查询语句;

一种是函数中可以有多条SQL语句;

标量函数

语法格式

--创建一个函数
create function 函数名(参数 类型[,参数2 类型2, ...])
returns 返回值类型
as
begin
  declare @变量 变量类型
  set @变量 = 参数
  return @变量
end

--调用方法
dbo.函数名(参数)
例如:
select dbo.函数名(参数) as "列名"

示例

--创建一个函数用来获取计量单位对应的换算率
create function Coeff(@FUnitID int)
returns decimal(28,10)
as
begin
    declare @Coeff decimal(28,10)
    select @Coeff = FCoefficient from t_MeasureUnit where FMeasureUnitID = @FUnitID 
    return @Coeff
end

--调用方法
declare @n decimal(28,10)
select @n = 5 * dbo.coeff(290) 
print @n

表格值函数

语法格式

create function 函数名(参数 类型[,参数2 类型2, ...])
returns table
as
return (SQL查询语句)

示例

create function func_ReqMto(@FDetailID int, @FItemID int)
returns table
as 
return (select   t1.FInterID , 
               t1.FItemID ,
               t1.FQty ,
               t1.FUnitID ,
               t1.FDetailID ,
               t1.FSecQty ,
               t1.FMTONo as "ToMTONo",
               t2.FBatchNo ,
               t2.FStockID ,
               t2.FStockPlaceID ,
               t2.FQty as "FQtyInv",
               t2.FKFPeriod ,
               t2.FKFDate ,
               t2.FSecQty as "SecQtyInv",
               t2.FMTONo as "FromMTONo"
        
               from PORequestEntry t1 left join ICInventory t2 on t1.FItemID = t2.FItemID 
               where t1.FItemID = @FItemID
                 and t1.FDetailID = @FDetailID
                 and t2.FMTONo in ('','999999')
               )
--调用方法
select * from func_ReqMto(参数1,参数2)

语法格式

create[alter] function 函数名(参数 类型[,参数2 类型2,...])
returens @表变量 (字段名 类型, [字段名2 类型2, 字段名3 类型3, ...])
as
begin
    SQL过程
    insert into @表变量 select 字段名 类型, [字段名2 类型2, 字段名3 类型3, ...] from [条件]
end

示例

create function func_Inv( @FDetailID int, @FItemID int)
returns @Inv table  (FInterID int, 
                     FItemID int,
                     FQty_Req decimal(28,10),
                     FUntiID int,
                     FDetailID int,
                     FSecQty_Req decimal(28,10),
                     FMTONo_Req nvarchar(50),
                     FBatchNo varchar(200),
                     FStockID int,
                     FStockPlaceID int,
                     FQty decimal(28,10),
                     FKFPeriod int,
                     FKFDate varchar(20),
                     FSecQty decimal(28,10),
                     FMTONoFrom nvarchar(50))
as
begin
insert into @inv 
      select   t1.FInterID , 
               t1.FItemID ,
               t1.FQty ,
               t1.FUnitID ,
               t1.FDetailID ,
               t1.FSecQty ,
               t1.FMTONo as "ToMTONo",
               t2.FBatchNo ,
               t2.FStockID ,
               t2.FStockPlaceID ,
               t2.FQty as "FQtyInv",
               t2.FKFPeriod ,
               t2.FKFDate ,
               t2.FSecQty as "SecQtyInv",
               t2.FMTONo as "FromMTONo"
        
               from PORequestEntry t1 left join ICInventory t2 on t1.FItemID = t2.FItemID 
               where t1.FItemID = 293 
                 and t1.FDetailID = 176
                 and t2.FMTONo in ('','999999')
               order by t2.FMTONo , t2.FQty desc
               
return
end

SQL存储过程

语法格式

create proc 过程名称
@变量1 类型, [可以有默认值]
@变量2 类型, [可以有默认值]
...
as 
begin
    SQL语句
end
--调用方法
exec 过程名称 @变量1 = 参数, @变量2 = 参数

示例

create proc proc_mtoChange
@FDetailID int ,
@isAll int ,
@maxnum int ,
@reqQty decimal(28,10)
as
begin

select identity(int,1,1) as "id", * ,null as "chaQty" , null as "MTOChange", null as "fno"
    into #inv
    from func_reqmto(@FDetailID)
    order by frommtono, fqtyinv desc
    
declare @m int = 1
declare @count int = (select count(id) from #inv)

declare @chaQty decimal(28,10)
select @chaQty = fqty from PORequestEntry where FDetailID = @FDetailID
while @m <= @count begin
    set @chaQty = @chaQty - (select fqtyinv from #inv where id = @m)

    update #inv set chaQty = @chaQty where id = @m
    if @chaQty > 0 begin
        update #inv set MTOChange = FQtyInv where id = @m
    end
    else if @chaQty <= 0 begin
        update #inv set MTOChange = FQtyInv + chaQty where id = @m 
    end
    set @m = @m +1
end 
update #inv set fno = id where chaQty <=0

declare @top int
declare @sumMTOchange decimal(28,10)

if @isall = 0 begin
    select @top = MIN(fno) from #inv
    /* [FEntrySelfP0133] MTO调整数量;[FEntrySelfP0134] MTO关联标志 */
    update PORequestEntry set FEntrySelfP0133 = @reqQty / dbo.coeff(@FDetailID),FMRPClosed = 1 ,FEntrySelfP0134 = 1
                            where FDetailID = @FDetailID
    drop table #sumMTOchange
end

else if @isall = 1 begin
    select @top = count(id) from #inv
    /* [FEntrySelfP0133] MTO调整数量;[FEntrySelfP0134] MTO关联标志 */
    update PORequestEntry set FAuxQty = (@reqQty-dbo.SumQty ( @FDetailID ))/dbo.coeff(@FDetailID), FEntrySelfP0133 = dbo.SumQty ( @FDetailID ) /dbo.coeff(@FDetailID), FEntrySelfP0134 = 1
                            where FDetailID = @FDetailID
end

select top(@top)
       @maxnum AS "FID",
       '' AS "FIndex",  
       FItemID,                 --物料内码
       0 AS "FAuxPropID",
       FBatchNo,                --批号
       FStockID,                --仓库
       FStockPlaceID,           --仓位
       FQtyInv,                 --即时库存基本数量
       null AS "FSecUnitID",    --浮动计量单位
       FUnitID,                 --采购申请单计量单位
       ( FQtyInv / dbo.coeff ( @FDetailID ) ) AS "FQty",  --即时库存常用计量单位数量
       FSecCoefficient,         --辅助计量单位换算率
       SecQtyInv,               --即时库存辅助计量单位数量
       0 AS "FChangeQty_Base",
       ( MTOChange / dbo.coeff ( @FDetailID ) ) AS "FChangeQty",
       case when FSecCoefficient = 0 then 0 else ( MTOChange / FSecCoefficient ) end AS "FChangeSecQty",
       case when (fkfdate = '') then null else FKFDate end AS "FKFDate",
       FKFPeriod,
       case when (fkfdate = '') then null else FKFDate + fkfperiod end AS "FPeriodDate",
       FromMTONo,
       ToMTONo,
       MTOChange,
       0 AS "FSelectedProcID",
       0 AS "FEntrySupply",
       0 AS "FStockTypeID",
       '' AS "FMrpNo"
       into #t_mtoChangeEntry
       from #inv

insert into t_MTOChangeEntry select * from #t_mtoChangeEntry
drop table #t_mtoChangeEntry
drop table #inv
end

四、一个示例

问题提出

大体思路

在单据审核的时候,
首先判断单据上是否存在可以调整的记录,
如果单据上至少有一行可以进行调整的记录,
则:
按顺序把每行物料拿出来,
到库存表里面去找MTO计划跟踪号为'999999'和''的记录,
将调整数量生成MTO调整单，
并更新采购申请单的关联数量，
以及行业务关闭标志和整单关闭标志

1.上下游单据之间的关联关系

• 生成下游单据之后, 采购申请单的执行情况, 行业务关闭标志状态, 单据头审核状态、关闭状态
• 下游单据删除之后, 如何恢复采购申请单的前置状态

2.多计量单位, 辅助计量单位

3.库存记录中存在多个批次, 多个仓库, 仓位 , 生产日期/保质期的情况

五、细节备忘

PORequest-采购申请单

采购申请单字段说明

--关于单据上数量字段的说明
select FInterID,
       FEntryID,
       FItemID,
       FQty,              -- (Qty)基本单位申请数量, ( = FAuxQty * FUnitID.FCoefficient ) 受数据库强制更新的约束, update时只能更新FAuxQty
       FCommitQty,        -- (Qty)基本单位`关联`数量, ( = 原值 + 下游单据`存时`关联的基本数量 ) []
       FUnitID ,          -- (Qty)单据上`录入`的计量单位 ( 可以是基本计量单位, 也可以是其他常用计量单位 )
       FAuxCommitQty,     -- (Qty)常用单位关联数量, ( = FCommitQty / FUnitID.FCoefficient ) 
       FAuxQty ,          -- (Qty)单据上`录入`的数量 ( 单位是FUnitID )
       FSecCoefficient ,  -- (Qty)单据上`录入`的辅助计量单位换算率 ( tips : 物料的辅助计量单位只能有一个, 用过后就不能修改了, 所以单据上显示的辅助计量单位是自动显示物料的基础信息, 且不能修改)
       FSecQty ,          -- (Qty)单据上`录入`的辅助计量单位对应的数量 ( tips : 辅助数量FSecQty和辅助换算率FSecCoefficient , 俩者之间会自动进行倒算)
       FSecCommitQty ,    -- (Qty)辅助单位`关联`数量, ( = 原值 + 下游单据`保存`时关联的辅助数量 )
       FOrderQty ,        -- (Qty)基本单位`执行`数量 ( = 原值 + 下游单据`审核`时关联的基本数量 )
       FEntrySelfP0133,   -- (Qty)自定义的字段2, ( = MTO调整单.FChangeQty ; 用来记录MTO调整的常用数量 )
       FEntrySelfP0134    -- 自定义的字段3, (用来记录当前行是否进行了MTO调整, 1为已经调整)   
from PORequestEntry
where FDetailID = @FDtailID

单据关闭标志说明

• Entry 单据体标志

• Head 整单标志

FClosed: 0 无下推或者部分下推 ; 1 全部下推 ;
FStatus: 0 未审核; 1 审核但是无关联或无关闭; 2 部分关闭 ; 3 关联或全部关闭
FChildren: 0 无关联单据 ; 1 有关联单据

ICInventory-存货表

存货余额表字段说明

select FItemID , 
       FBatchNo ,       -- 批号
       FStockID ,       -- 仓库
       FQty ,           -- 基本单位数量 (存货表中的数量只统计基本单位数量和浮动计量单位数量)
       FBal , 
       FStockPlaceID ,  -- 仓位 
       FKFPeriod ,      -- 有效期
       FKFDate ,        -- 生产日期
       FQtyLock , 
       FAuxPropID , 
       FSecQty ,        -- 浮动计量单位数量 
       FMTONo ,         -- MTO计划跟踪号
       FSupplyID 
 from ICInventory

T_MTOChange-MTO调整单

MTO调整单字段说明

select FID , -- 单据内码
       FIndex , 
       FItemID ,  
       FAuxPropID ,
       FBatchNo ,        -- 要调整的批次
       FStockID ,        -- 要调整的仓库
       FSPID ,           -- 要调整的仓位
       FBaseQty ,        -- (Qty)即时库存的基本单位数量 ( 来源于库存表, 数量精确定位到物料.批号.MTO计划跟踪号.库房.仓位  )
       FSecUnitID ,      -- 'NULL' ; ( 好像没什么用 )
       FUnitID ,         -- (Qty)单据上`录入`计量单位 ( = 采购申请单.FUnitID )
       FQty ,            -- (Qty)根据FUnitID计算出来的即时数量, ( = FBaseQty * FUnitID.FCefficient )
       FSecCoefficient , -- (Qty)辅助单位换算率 ( = FBaseQty / FSecQty )
       FSecQty ,         -- (Qty)即时库存的辅助单位数量 ( 来源于库存表 )
       FChangeQty_Base , -- 0.00不清楚有什么用
       FChangeQty ,      -- (Qty)单据上`录入`调整数量 ( 单位是 FUnitID ) [ 本案取 = FChangeBaseQty / FUnitID.FCefficient ]
       FChangeSecQty ,   -- (Qty)辅助单位调整数量 ( = FChangeBaseQty / FSecCoefficient )
       FKFDate ,         -- 库存中物料的生产日期 ( case when 库存.KFFDate = '' then null else 库存.KFFDate end )
       FKFPeriod ,       -- 库存中物料的有效期
       FPeriodDate ,     -- 单据上物料的有效期至 ( case when 库存.fkfdate = '' then null else 库存.FKFDate + 库存.fkfperiod end )
       FFromMTONo ,      -- 库存中物料的MTONo
       FToMTONo ,        -- 单据上录入的MTONo
       FChangeBaseQty    -- (Qty)基本单位调整数量, ( = FChangeQty * FUnitID.FCefficient ) [ 本案取( 采购申请.FQty - 库存.FQty ) 比较之后的数量]
from t_MTOChangeEntry 

六、成果展示

订单BOM如下

展示 - mto订单.png

测试账套下载

提取码：回复可见