SQL触发器及存储过程学习，一个门外汉的学习笔记

SQL语句
301
0
0
2022-04-04
SQL语法学习 - 不用真的记不住
上一篇
数据库之SQL基础语句Select
下一篇
这个文章的起源是：金蝶k3wise采购申请单审核时需要自动校验库存，并且生成mto调整单；
直接导致：一个从未写过触发器的门外汉，得到了一点提高；

这篇文章适合像我这样只会在数据库里面进行增删改查，但是还想在数据库中更深入一下的童鞋，通过学习可以初步掌握触发器和存储过程的使用。

文中的示例都是建立在实际项目上的，可能不太适合练习。如果有合适的时间，并且还有一些阅读量的话，我会考虑把案例做成标准的数据。

如果你不慎看到这篇文章，但是对数据库还不熟悉，那么你可以先学习一下基本的语法：SQL语法学习 - 不用真的记不住

一、触发器的一些基础

1.1 触发器的概念

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

1.2 触发器类型

  • 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触发器，把更新操作替换为向每个基本表进行更新。

1.3 触发器的2个中间表

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

SQL触发器及存储过程学习，一个门外汉的学习笔记

1.4 触发器的语法结构

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

1. `FOR`和`AFTER`意义一样; 它们不能作用于视图

2. 修改触发器把`CREATE`改成`ALTER`

3. 触发器命名规则: `tr_表名_说明`

触发器常用语句

  • 删除触发器
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
begindeclare @billno nvarchar(100)
    if UPDATE(FCheckerID) beginselect @billno = fbillno from deleted
        update PORequest set FBillNo = 'rp001'where FBillNo = @billno
end
end

这是一个字段级的触发器, 为了能识别单据是保存时执行了update还是审核时执行了update,

所以使用了if update(字段); 当FCheckerID字段改变后, 才会触发后面的SQL语句

三、SQL相关知识

3.1 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 --获取临时表的记录数
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

3.2 SQL函数

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

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

  • 标量函数:

返回单个数据

  • 表格值函数:

一种是函数中只有一条查询语句;

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

3.2.1 标量函数

返回单个数据

语法格式

  • 创建一个函数
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

3.2.2 表格值函数

（1）单条语句, 返回值为一个table

语法格式

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)
（2）多条语句, 返回值为一个table

语法格式

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
begininsert 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 = 293and t1.FDetailID = 176and t2.FMTONo in ('','999999')
    order by t2.FMTONo , t2.FQty descreturn
end

3.3 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
beginselect identity(int,1,1) as "id", * ,null as "chaQty" , null as "MTOChange", null as "fno" into #invfrom func_reqmto(@FDetailID)
  order by frommtono, fqtyinv descdeclare @m int = 1declare @count int = (select count(id) from #inv)declare @chaQty decimal(28,10)
  select @chaQty = fqty from PORequestEntry where FDetailID = @FDetailID
  while @m <= @count beginset @chaQty = @chaQty - (select fqtyinv from #inv where id = @m)update #inv set chaQty = @chaQty where id = @mif @chaQty > 0 beginupdate #inv set MTOChange = FQtyInv where id = @mendelse if @chaQty <= 0 beginupdate #inv set MTOChange = FQtyInv + chaQty where id = @mendset @m = @m +1endupdate #inv set fno = id where chaQty <=0declare @top intdeclare @sumMTOchange decimal(28,10)
  if @isall = 0 beginselect @top = MIN(fno) from #inv/* [FEntrySelfP0133] MTO调整数量;[FEntrySelfP0134] MTO关联标志 */ 
    update PORequestEntry set FEntrySelfP0133 = @reqQty / dbo.coeff(@FDetailID),FMRPClosed = 1 ,FEntrySelfP0134 = 1where FDetailID = @FDetailID 
    drop table #sumMTOchangeendelse if @isall = 1 beginselect @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 = 1where FDetailID = @FDetailID
  endselect 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_mtoChangeEntryfrom #invinsert into t_MTOChangeEntry select * from #t_mtoChangeEntrydrop table #t_mtoChangeEntrydrop table #inv
end
SQL语法学习 - 不用真的记不住
上一篇
数据库之SQL基础语句Select
下一篇