k3物料收发明细表sql_SQL触发器及存储过程学习，⼀个门外
汉的学习笔记
这个⽂章的起源是：⾦蝶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表是表⽰删除的数据。
1.4 触发器的语法结构
CREATE TRIGGER trigger_nameON {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 trignamealter table tablename enable trigger trigname
sql profiler如何捕捉触发器
sqlserver profiler 的event⾥选择这两个：
Stored procedures:
-
SP:StmtStarting
- SP:StmtCompleted
⼆、初步测试
初步在`采购申请单`上创建⼀个触发器, 在`单据审核`时`修改`单据的`编码`
sql容易学吗create trigger tr_POrequest_MTOchangeon POrequestafter updateasbegin declare @billno nvarchar(100) if UPDATE(FCheckerID) begin select @billno =这是⼀个字段级的触发器, 为了能识别单据是保存时执⾏了update还是审核时执⾏了update,
所以使⽤了if update(字段); 当FCheckerID字段改变后, 才会触发后⾯的SQL语句
三、SQL相关知识
3.1 SQL表循环
需要在采购申请明细表中遍历所有⾏的物料, 通过循环的⽅法实现. (游标不会)
set nocount on--创建⼀个表变量, ⽤来存储要遍历的内容declare @t_MTOEntry table (FDetailID int)in
sert into @t_MTOEntry from select ... --插⼊表内容, 省略(这⾥3.2 SQL函数
SQL⾃定义函数主要分为2种 :
标量函数:
返回单个数据
表格值函数:
⼀种是函数中只有⼀条查询语句;
⼀种是函数中可以有多条SQL语句;
3.2.1 标量函数
返回单个数据
语法格式
创建⼀个函数
create function 函数名(参数类型[，参数2 类型2, ...])returns 返回值类型asbegindeclare @变量变量类型set @变量 = 参数return @变量end
调⽤⽅法
dbo.函数名(参数)
select dbo.函数名(参数) as "列名"
⽰例
创建⼀个函数⽤来获取计量单位对应的换算率
create function Coeff(@FUnitID int)returns decimal(28,10)asbegindeclare @Coeff decimal(28,10)select @Coeff = FCoefficient from t_MeasureUnit where
调⽤⽅法
declare @n decimal(28,10)select @n = 5 * ff(290)print @n
3.2.2 表格值函数
(1)单条语句, 返回值为⼀个table
语法格式
create function 函数名(参数类型[，参数2 类型2, ...])returns tableasreturn (SQL查询语句)
⽰例
create function func_ReqMto(@FDetailID int, @FItemID int)returns tableasreturn (select t1.FInterID ,t1.FItemID ,t1.FQty ,t1.FUnitID ,t1.FDetailID ,t1.FSecQty ,t1.F 调⽤⽅法
select * from func_ReqMto(参数1，参数2)
(2)多条语句, 返回值为⼀个table
语法格式
create[alter] function 函数名(参数类型[，参数2 类型2,...])returens @表变量 (字段名类型, [字段名2 类型2, 字段名3 类型3, ...])asbeginSQL过程insert into @表变量 se ⽰例
create function func_Inv( @FDetailID int, @FItemID int)returns @Inv table (FInterID int, FItemID int,
FQty_Req decimal(28,10), FUntiID int, FDetailID int, FSec
3.3 SQL存储过程
语法格式
create proc 过程名称@变量1 类型, [可以有默认值]@变量2 类型, [可以有默认值]...asbeginSQL语句end
调⽤⽅法
exec 过程名称 @变量1 = 参数, @变量2 = 参数
⽰例
create proc proc_mtoChange @FDetailID int , @isAll int , @maxnum int , @reqQty decimal(28,10)asbegin select identity(int,1,1) as "id", * ,null as "chaQty" , nu
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论