SQLServer：触发器及实际应用

实际应用

在实施过程中，客户提出一个需求：新开会员赠送会员钱包和优惠卷

思路：ERP软件中本身就有往会员钱包中进行充值以及赠送会员优惠卷的操作，查看这些操作对应的存储过程，即可知道存储过程中如何进行insert操作；现在要做的就是：当顾客新增会员之后，如何自动执行这两个操作；

操作：在GL_HY表中挂载一个insert触发器，在触发器中编写赠送会员钱包和优惠卷的SQL逻辑代码；当客户在ERP系统中新增会员，底层就会往GL_HY这张表中进行insert操作，此时就会触发Insert触发器，就会执行触发器对应的SQL代码；

部分触发器SQL：

USE [G3ERP_advanced]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[xinkaihuiyuan]
ON [dbo].[GL_HY]
AFTER INSERT
AS
BEGIN
    DECLARE @MEMBERID VARCHAR(50)--会员编号
	DECLARE @BILLNO VARCHAR(50)--vipStoredbill（会员钱包变动明细表）单据流水号
	DECLARE @BRANCHGUID VARCHAR(50)--组织机构GUID
	DECLARE @OPERATORGUID VARCHAR(50)--操作人员GUID
	DECLARE @OPERATORCODE VARCHAR(50)--操作人员编号（用于获取单据流水号）
	--SELECT * INTO #inserted FROM inserted
	--解释：
	--1、在SQLServer的触发器中，你可以使用INSERTED和DELETED表来获取触发事件的数据，具体取决于触发的事件类型
	--2、这里相当于inserted表中存储了GL_HY表新增的数据
	SELECT     @MEMBERID=inserted.ID,@BRANCHGUID=inserted.RegisterSubOrgan,@OPERATORGUID=inserted.OperatorGuid FROM inserted	
	--获取操作人员对应的操作人员编号（获取单据流水号时会用到）
	SELECT @OPERATORCODE=Code FROM Operators WHERE Guid=@OPERATORGUID;	
	--"会员钱包变动明细"单据流水号：@BILLNO借用"会员钱包充值"表单编码规则
	--解释：
	--1、内置了一个函数：GetBillNo用来获取单据的编码（这个函数可以自己编写）
	--2、调用该函数需要传入三个参数：单据流水号对应的编码规则、操作人员编号、日期
	SELECT @BILLNO=dbo.GetBillNo('43006065',@OPERATORCODE,GETDATE())
	--3、手动获取单据的编号之后，记得一定要修改编码规则对应的最大序号
	--SELECT * FROM BILLNUMBER WHERE BillName ='会员钱包充值'
	UPDATE BILLNUMBER SET MaxNum=MaxNum+1 WHERE BillGuid='43006065'

	

	---------------------**************充值***************------------------------
		--------1.往钱包插入一条空值
		--解释：
		--1、vipStoredValue这张表中存储了会员的会员钱包的存入和支出金额，是分开来存储的，如果两个字段之和，就是会员剩余的会员钱包金额
		--2、如果会员是第一次往会员钱包充值，需要先往vipStoredValue表中插入一条空的会员记录	
		if not exists(select * from  vipStoredValue where vipid=@MEMBERID)
		begin
			INSERT INTO vipStoredValue(vipid,storedadd)
			values(@MEMBERID,0)
		end
		
		------2.更新钱包 充值额
		--解释：
		--1、更新vipStoredValue表中相关字段，这张表中有两个字段：storedadd、storedreduce
		--2、storedadd：表示存入的累积金额
		--3、storedreduce：表示支出的累积金额
		--UPDATE vipStoredValue SET storedadd=isnull(storedadd,0)+isnull(@RechargeValue,0)  WHERE VIPID=@MEMBERID
		UPDATE vipStoredValue SET storedadd=isnull(storedadd,0)+50  WHERE VIPID=@MEMBERID--新开会员默认赠送50元
		
		------3.插入充值金额
		--解释：
		--1、VipStoredBill会员钱包变动明细
		--2、"会员钱包账页"报表数据源主表就是VipStoredBill		
		INSERT INTO vipStoredbill
		(
		GUID, --VipStoredBill单据GUID
		BILLNO,--VipStoredBill单据流水号
		BILLDATE,--日期
		BILLTIME,--事件
		BILLTYPE,--类型
		VIPID,--会员编号
		AMOUNT,--金额
		BRANCHGUID,--组织机构
		OPERATORGUID,--操作人员GUID
		notes--备注
		)
		SELECT 
		NEWID(),
		@BILLNO,
		CONVERT(VARCHAR(10),GETDATE(),23),
		CONVERT(VARCHAR(10),GETDATE(),108),
		1,
		@MEMBERID,
		50,
		@BRANCHGUID,
		@OPERATORGUID,
		'新开会员赠送会员钱包'
		--FROM VIPPurseRecharge A LEFT JOIN OPERATORS B ON B.GUID=A.OPERATORGUID WHERE A.GUID=@BILLGUID
		
	--会员钱包充值需要用到的表
	--SELECT RegisterSubOrgan,OperatorGuid,* FROM GL_HY;
	--SELECT * FROM vipStoredValue;
	--SELECT * FROM vipStoredbill;
END

什么是触发器？

对触发器的理解：

触发器：其实就是一个特殊的"存储过程"，触发器的执行并不是程序调用或者人为手动调用，而是由"事件"来触发执行；这里的事件可以理解为："表事件"；即，当我们对某张表进行DML操作，就会激活对应触发器的执行；

如何使用触发器？

触发器的分类：DML触发器、DDL触发器、登录触发器

其中，最最最常用的就是DML触发器，而DML触发器又可以细分为：Insert 触发器、Delete 触发器、Update 触发器

Insert 触发器：在向目标表中插入数据后，会触发该表的Insert 触发器，系统自动在内存中创建inserted表； 如果不满足判断数据会进行回滚，插入的数据操作会失败

Delete 触发器：在向目标表中删除数据后，会触发该表的Delete 触发器，系统自动在内存中创建deleted表，deleted表存放的是删除的数据

Update 触发器：在向目标表中更新数据后，会触发该表的Update 触发器，系统自动在内存中创建deleted表和inserted表，deleted表存放的是更新前的数据，inserted表存放的是更新后的数据

使用触发器：比较简单的，其实最主要的就是通过在内存中创建的临时表进行数据的相关操作

总结：在SQL Server触发器中，你可以使用 INSERTED 和 DELETED 表来获取触发事件的数据，具体取决于触发的事件类型（即，触发器类型）

触发器的优缺点

优点：

1、触发器可以确保数据的完整性

2、触发器可以帮助我们记录操作日志

3、触发器还可以用在操作数据前，对数据进行合法性检查

解释：比如，超市进货的时候，需要库管录入进货价格。但是，人为操作很容易犯错误，比如说在录入数量的时候，把条形码扫进去了；录入金额的时候，看串了行，录入的价格远超售价，导致账面上的巨亏……这些都可以通过触发器，在实际插入或者更新操作之前，对相应的数据进行检查，及时提示错误，防止错误数据进入系统

缺点：

1、触发器最大的一个问题就是可读性差

解释：因为触发器存储在数据库中，并且由事件驱动（区别于：存储过程可以通过exec手动调用）；这就意味着触发器有可能不受应用层的控制，这对系统维护是非常有挑战的

2、相关数据的变更，可能会导致触发器出错

解释：特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率