1. 概述1.1 触发器简介触发器( trigger )是 SQL server 提供给程序员和数据分析员用来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作( insert，delete， update)时就会激活它执行，触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以查询其他表，而且可以包含复杂的 SQL 语句。它们主要用于强制服从复杂的业务规则或要求。 触发器也可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。然而，强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图，则可以在表之间创建关系以自动创建外键约束。
1.2 工具准备2. 示例2.1 数据准备本文代码较多，但多为简单例子，旨在简单说明触发器如何创建及其效果验证。 2.1.1 数据库表判断
首先判断，要使用的数据库中是否存在要使用的表，本触发器以水果销售表与水果库存表为例，SQL 代码如下： - USE test
- GO
- --本触发器以水果销售表与水果库存表为例
- IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'SGKC')
- DROP TABLE SGKC --判断是否存在水果库存表
- GO
- IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = 'SGXS')
- DROP TABLE SGXS --判断是否存在水果销售表
- GO
上面判断完成后，下面开始建表，SQL 代码如下： - CREATE TABLE SGXS --创建水果销售表
- (
- PP VARCHAR(40) PRIMARY KEY NOT NULL, --水果品牌
- GHS VARCHAR(40) NULL, --供货商
- XSSL INT NULL, --销售数量
- XSDJ MONEY NULL, --销售单价
- XSJE MONEY NULL --销售金额
- )
- GO
- --SELECT * FROM SGXS
- CREATE TABLE SGKC --创建水果库存表
- (
- PP VARCHAR(40) PRIMARY KEY NOT NULL, --水果品牌
- KCSL INT NULL, --库存数量
- KCDJ MONEY NULL, --库存单价
- KCJE MONEY NULL --库存金额
- )
- GO
- --SELECT * FROM SGKC
数据库表创建完成后，下面开始效果测试。 2.2.1 测试一创建触发器[T_INSERT_SGKC] 触发器功能：强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。 注1：每当 SGKC 表发生 INSERT 动作，则引发该触发器。 注2：[INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。 注3：这两个系统表的结构同插入数据的表的结构。 SQL 代码如下： - IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR'AND NAME='T_INSERT_SGKC')
- DROP TRIGGER T_INSERT_SGKC --如果存在 T_INSERT_SGKC 触发器则删除
- GO
- CREATE TRIGGER T_INSERT_SGKC
- ON SGKC
- FOR INSERT
- AS
- BEGIN TRANSACTION --提交事务处理
- UPDATE SGKC
- SET KCJE=KCDJ * KCSL
- WHERE PP IN(SELECT PP FROM INSERTED)
- COMMIT TRANSACTION
- GO
针对 SGKC 表，插入测试数据： 第一条数据（火龙果）中的数据符合业务规则。 第二条数据（桂圆）中，[库存金额]空，不符合业务规则。 第三条数据（西瓜）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。 第四条数据库存数量为 0。 注：插入数据后，检查 SGKC 表中的数据是否 库存金额 = 库存数量* 库存单价。 SQL 代码如下： - INSERT INTO SGKC(PP,KCSL,KCDJ,KCJE)
- SELECT '火龙果',10,12,120 UNION ALL
- SELECT '桂圆',20,22,NULL UNION ALL
- SELECT '西瓜',12,60,500 UNION ALL
- SELECT '橘子',0,30,0 GO
- --SELECT * FROM SGKC --检验测试数据可以触发触发器
测试效果如下图所示： ![]()
2.2.3 测试二创建触发器[T_INSERT_SGXS] 触发器功能：实现业务规则。 业务规则：如果销售的水果品牌不存在库存或者库存为零，则返回错误， 否则则自动减少[卷烟库存表]中对应品牌水果的库存数量和库存金额。 注：每当 SGKC 表发生 INSERT 动作，则引发该触发器。 创建触发器：
- IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE XTYPE='TR' AND NAME='T_INSERT_SGXS') DROP TRIGGER T_INSERT_SGXS GO
- CREATE TRIGGER T_INSERT_SGXS --创建触发器 T_INSERT_SGXS ON SGXS FOR INSERT ASBEGIN TRANSACTION --判断库存是否存在或者是否大于 0
- IF NOT EXISTS( SELECT KCSL FROM SGKC WHERE PP IN(SELECT PP FROM INSERTED) )BEGIN RAISERROR('错误！该水果不存在库存，不能销售',16,1)
- ROLLBACK RETURN END
- IF EXISTS( SELECT KCSL FROM SGKC WHERE PP IN(SELECT PP FROM INSERTED) AND KCSL<=0 )BEGIN RAISERROR('错误！该水果库存小于等于 0，不能销售',16,1) ROLLBACK RETURN END
SQL 代码如下：
- UPDATE SGXS SET XSJE = XSSL * XSDJ WHERE PP IN(SELECT PP FROM INSERTED)
- DECLARE @PP VARCHAR(20) SET @PP = (SELECT PP FROM INSERTED)
- DECLARE @XSSL INT SET @XSSL = (SELECT XSSL FROM INSERTED)
- UPDATE SGKC SET KCSL = KCSL-@XSSL, KCJE=(KCSL-@XSSL)*KCDJ WHERE PP = @PP COMMIT TRANSACTION
- GO
针对 SGXS 表，插入第一次测试数据，先插入正确数据。 SQL 代码如下：
- INSERT INTO SGXS(PP,GHS,XSSL,XSDJ,XSJE) SELECT '西瓜','某供货商',7,60,420
- GO
- SELECT * FROM SGXS --验证是否销售表中已经插入该信息
- SELECT * FROM SGKC --验证库存表中该水果是否已经扣除数量并修改相应库存金额
- GO
测试效果如下图所示：
![]()
2.2.5 验证测试二针对 SGXS 表，插入第二次测试数据，插入错误数据，该数据销售金额 不等于销售单价*销售数量。 注：触发器将自动更正数据，使得销售金额不等于销售单价*销售数量。 SQL 代码如下：
- INSERT INTO SGXS(PP,GHS,XSSL,XSDJ,XSJE) SELECT '桂圆','某供货商',10,22,2000 GO
- SELECT * FROM SGXS --验证是否销售表中已经插入该信息,并且自动修改销售金额
- SELECT * FROM SGKC --验证库存表中该水果是否已经扣除数量并修改相应库存金额
测试效果如下图所示： ![]()
![]()
2.2.6 验证测试二针对 SGXS 表，插入第三次测试数据，该数据中的水果品牌在水果库存中找不到对应，触发器报错。 SQL 代码如下： - INSERT INTO SGXS(PP,GHS,XSSL,XSDJ,XSJE) SELECT '苹果','某供货商',7,20,140
- GO
测试效果如下图所示： ![]()
2.2.7 验证测试二针对 SGXS 表，插入第四次测试数据，该数据中的水果品牌在水果库存中库存为 0，触发器报错。 SQL 代码如下： - INSERT INTO SGXS(PP,GHS,XSSL,XSDJ,XSJE) SELECT '橘子','某供货商',30,30,900
- GO
测试效果如下图所示： ![]()
3. 示例下载点击下载上述所有实例：
实例.rar
(1.8 KB, 下载次数: 64)
编辑于 2020-9-25 17:09
|
