实训项目12 触发器的应用

实验6 存储过程与触发器的应用 实训项目12 触发器的应用 一、实训目的 1、​ 掌握创建触发器的方法和步骤。 2、​ 掌握触发器的使用方法。 3、​ 掌握关系图的创建方法及作用。 二、实训准备 1、​ 了解触发器的基本概念和类型。 2、​ 了解创建触发器的SQL语句的基本语法。 3、​ 了解查看、修改和删除触发器的SQL命令的用法。 三、实训内容 虚拟测试环境：为某公司的产品管理创建数据库，该数据库中包含供生产部及销售部调度的产品库存表及产品销售表，要求库存产品与销产品间数据要同步。 1、正确创建数据库 2、正确创建产品库存表及产品销售表 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U' AND NAME = '库存表') DROP TABLE 库存表 GO --业务 规则 编码规则下载淘宝规则下载天猫规则下载麻将竞赛规则pdf麻将竞赛规则pdf ：库存金额 = 库存数量 * 库存单价，通过触发器正确实现。 CREATE TABLE 库存表 ( 产品型号 VARCHAR(10) PRIMARY KEY NOT NULL, 产品名称 VARCHAR(40) NOT NULL, 库存数量 INT NULL, 库存单价 MONEY NULL, 库存金额 MONEY NULL ) GO IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U' AND NAME = '销售表') DROP TABLE 销售表 GO --业务规则：销售金额 = 销售数量 * 销售单价，通过触发器正确实现。 CREATE TABLE 销售表 ( 产品型号 VARCHAR(10) NOT NULL, 购货商 VARCHAR(40) NULL, 销售数量 INT NULL, 销售单价 MONEY NULL, 销售金额 MONEY NULL, 销售日期 DATETIME ) GO 3、创建触发器 （1）创建为库存表创建插入触发器[T_INSERT_库存表]。 ①说明： 每当[库存表]发生 INSERT 动作，则引发该触发器。 ②触发器功能： 强制执行业务规则，保证插入的数据中库存金额 = 库存数量 * 库存单价。 ③重要提示： [INSERTED]、[DELETED]为系统临时表，不可创建、修改、删除，但可以调用。这两个系统表的结构同插入数据的表的结构。 ④参考代码： IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME = 'T_INSERT_库存表') DROP TRIGGER T_INSERT_库存表 GO CREATE TRIGGER T_INSERT_库存表 ON 库存表 AFTER INSERT AS --强制执行下列语句，保证业务规则 UPDATE 库存表 SET 库存金额 = 库存数量 * 库存单价 WHERE 产品型号 IN (SELECT 产品型号 from INSERTED) GO ⑤针对[库存表]，插入测试数据： 注意，第一条数据中的数据符合业务规则， 第二条数据中，[库存金额]空，不符合业务规则， 第三条数据 [库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。 第四条数据库存数量为0。 请注意在插入数据后，检查[库存表]中的数据是否符合库存金额 = 库存数量 * 库存单价。 INSERT INTO 库存表(产品型号,产品名称,库存数量,库存单价,库存金额) SELECT 'N97','诺基亚N97',20,4000,80000 UNION ALL SELECT 'N85', '诺基亚N85',10,3000,NULL UNION ALL SELECT 'MT7', '摩托罗拉MT7',5,9000,500 UNION ALL SELECT 'N95','诺基亚N95',0,null,null GO --查询数据 SELECT * FROM 库存表 GO ⑥上述插入语句也可用四条INSERT……VALUES……实现。 (2)创建触发器[T_INSERT_销售表]，该触发器较复杂。 ①说明: 每当[库存表]发生 INSERT 动作，则引发该触发器。 ②触发器功能： 实现业务规则。 ③业务规则: 如果销售的产品不存在或者库存为零或者库存不足，则终止销售操作并返回相应提示。 否则则自动减少[库存表]中对应库产品的库存数量和库存金额。 ④下面给出部分代码提示，请大家补充完成该触发器。 IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'TR' AND NAME ='T_INSERT_销售表') DROP TRIGGER T_INSERT_销售表 GO CREATE TRIGGER T_INSERT_销售表 ON 销售表 AFTER INSERT AS --检查数据的合法性： --1、如果销售的产品不存在，返回提示，撤消销售操作 IF NOT EXISTS ( SELECT * FROM 库存表 WHERE 产品型号 IN (SELECT 产品型号 FROM INSERTED) ) BEGIN --返回错误提示 RAISERROR('该型号的产品不存在，不能销售，请重新输入。',16,1) --回滚事务 ROLLBACK RETURN END --2、销售的产品存在，但库存为0，返回提示，撤消销售操作，代码与1类似 --3、销售的产品存在，但库存数量比销售数量少，返回提示，撤消销售操作 --提示：可如下所示先定义一个变量存放销售数量，其他代码与1类似 --DECLARE @xssl MONEY --SET @xssl = (SELECT 销售数量 FROM INSERTED) --对合法的数据进行处理 ： --1、更新销售表中的相应产品的销售金额 UPDATE 销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 产品型号 IN (SELECT 产品型号 FROM INSERTED) --2、更新库存表中的相应产品的为库存数量及库存金额 /*提示：可先定义两个变量，分别存放销售表中销售产品的型号及数量,而销售数据 已在前面定义过，故不能再重复定义，此处只需定义存放销售型号的变量既可*/ ⑤请大家自行跟踪[库存表]和[销售表]的数据变化。 --针对[销售表]，插入第一条测试数据，该数据是正常的。 --针对[销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量（正确结论应该是；触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量）。 I--针对[销售表]，插入第三条测试数据，该数据中的产品在库存表中找不到对应（触发器应正确给出提示）。 --针对[销售表]，插入第四条测试数据，该数据中的产品在库存表中库存为0（触发器应正确给出提示）。 --针对[销售表]，插入第五条测试数据，该数据中的产品销售数量大于库存表中的库存数量（触发器应正确给出提示）。 --查询数据 SELECT * FROM库存表 SELECT * FROM销售表 GO 4、补充： （1）本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理； （2）关于触发器要理解并运用好 INSERTED ，DELETED 两个系统表； 5、请参阅销售表的插入触发器，编写销售表的更新触发器。