数据库触发器设计实验

一、实验目的和要求

通过本实验了解数据库触发器的概念，熟悉触发器的语法格式，掌握触发器完整性设计方法。

二、实验原理

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程，由服务器自动激活可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力。

三、实验环境、内容和方法实验环境

windows10 操作系统, mysql-8.1.0-winx64数据库服务器，老师提供的数据库脚本TradeDBSQL.txt。

内容：根据实验指导完成触发器应用于数据库完整性设计和触发器应用于业务逻辑，并测试触发器是否生效。

四、实验过程描述

（一） 触发器应用于数据库完整性设计

1.参照完整性级联约束

1）更新级联触发器 orderd_upd Create trigger orderd_upd after update on Orders for each row update OrderDetails set OrderID = new.OrderID where OrderID=old.OrderID;

设计测试用例，测试是否生效。

例如将orderid 从10400 改为 14400select * from orders where orderid=10400;update orders set orderid=14400 where orderid=10400 ;select * from orders where orderid=14400;select * from orderdetails where orderid=14400;

2）删除级联触发器 orderd_del Create trigger delt1 after delete on orders for each row delete from OrderDetails where OrderID=old.OrderID;

设计测试用例，测试是否生效。

在Orders表插入一条新数据

insert into Orders values (11088,'RATTC',1,'1998-5-6','1998-6-3','1998-5-28',2,8.53 ,'王先生','宽石西路 37 号','深圳','华南','871100','中国');

在OrderDetails表插入其详细信息

insert into OrderDetails values (11088,17,14.00,12,0);

查询select * from Orders  WHERE OrderID = 11088;select * from OrderDetails  WHERE OrderID = 11088;

在Orders表中删除11088的记录，测试触发器 delt1是否生效DELETE FROM Orders WHERE OrderID = 11088;

预期结果：订单详细信息表中的对应订单ID11088的记录被删除则说明触发器可以生效SELECT * FROM OrderDetails WHERE OrderID = 11088;

2.用户定义完整性Mysql 没有check 约束，只能通过触发器实现check 约束  

例如 产品表Products 中，规定单价UnitPrice的范围是大于0，小于100，设计触发器实现该约束  

delimiter %% ;create trigger prod_ins before insert on Products for each row  if  new.UnitPrice <=0 or new.UnitPrice>=100 then SIGNAL SQLSTATE '45000'   SET MESSAGE_TEXT = '不能插入数据，单价只能是大于0小于100'; end if;end %%

测试是否生效：delimiter ;

单价超过100

insert into Products values (88,'梅州金柚',1,1,'每箱20个',110,39,0,10,TRUE);

单价等于0

insert into Products values (89,'梅州金柚2',1,1,'每箱20个',0,39,0,10,TRUE);

二） 触发器应用于业务逻辑

1.新增加订单

例如 客户ID为ALFKI的客户下了一个订单，订单号为11088,订购了3种产品，具体数据如下：产品ID， 单价，数量，折扣13， 6， 10， 0.118, 62.5， 15， 0.1327， 43.5， 5, 0雇员ID为 6 的雇员处理订单，订购日期：今天到货日期：5天后发货日期：2天后运货商：2，运货费：38，业务逻辑

1）在订单orders中添加一条新纪录

2）在订单细节Orderdetails表中添加订购产品的相关记录

3）更新修改产品表中相关产品的库存量，减去订购的数量在OrderDetails 表上设计一个插入触发器，自动实现3）的更新库存的操作。create trigger insertorder before insert on OrderDetails for each row update Products set UnitsInStock = UnitsInStock - new.Quantity where ProductID = new.ProductID;

测试用例

insert into Orders values (11088,'ALFKI',6,'2018-10-14','2018-10-21','2018-10-19',3,32.38 ,'余小姐','光明北路 124 号','北京','华北','111080','中国');

记录Products 表中 13，18，27的库存量 select UnitsInStock from Products where ProductID=13;select UnitsInStock from Products where ProductID=18;select UnitsInStock from Products where ProductID=27;

填写新订单订单ID, 产品ID，单价，数量，折扣insert into OrderDetails values (11088, 13, 6, 10, 0.1);

insert into OrderDetails values (11088, 18, 62.5, 15, 0.13);

insert into OrderDetails values (11088,  27,    43.5,  5,  0);

完成上述插入操作后，比较 Products 表中 13，18，5的库存量

 select UnitsInStock from Products where ProductID=13;

select UnitsInStock from Products where ProductID=18;

select UnitsInStock from Products where ProductID=5;

2.修改订单    

客户ID为ALFKI的客户对订单号为11088 的订单做如下修改

1 .取消了27号产品，

2.增加了14号产品，单价为23.5，数量8，折扣为0.13. 将18号产品的数量改为10，折扣改为0.11修改订单的业务逻辑  

  1）从订单细节表中取消了某个产品，在产品表中返还该产品的库存量

delete from  OrderDetails where OrderID=11088 and ProductID=27;update Products set  UnitsInStock=  UnitsInStock + 5 where ProductID=27;

2）从订单细节表中增加了某个产品，增加了14号产品，单价为23.5，数量8，折扣为0.1

insert into OrderDetails values (11088,  14,    23.5,  8,  0);

update Products set  UnitsInStock= UnitsInStock - 8 where ProductID=14;

3）将18号产品的数量改为10，折扣改为0.11

update OrderDetails set Quantity=10, Discount=0.11 where ProductID=18 and OrderID=11088;

或者update OrderDetails set Quantity=Quantity -5 Discount=0.11 where ProductID=18 and OrderID=11088;

对于1） 增加删除触发器

Create trigger deletorder after delete  on OrderDetails for each row update Products set UnitsInStock = UnitsInStock +  old.Quantity where ProductID = old.ProductID;

对于2）插入触发器

create trigger insertorder before insert on OrderDetails for each row  update Products set UnitsInStock = UnitsInStock -  new.Quantity where ProductID = new.ProductID;

对于3）增加更新触发器

Create trigger Updateorder after update  on OrderDetails for each row update Products set UnitsInStock = UnitsInStock +  old.Quantity - new.Quantity   where ProductID = old.ProductID;

设计用例测试。

1）测试删除触发器删除订单细节表中的某个产品

delete from OrderDetails where OrderID = 11088 and ProductID = 27;

验证产品库存是否返还

select * from Products where ProductID = 27;

2）测试插入触发器向订单细节表中增加某个产品

insert into OrderDetails values (11088, 14, 23.5, 8, 0);

验证产品库存是否减少

select * from Products where ProductID = 14;

3）测试更新触发器

将18号产品的数量改为10，折扣改为0.11

update OrderDetails set Quantity = 10, Discount = 0.11 where ProductID = 18 and OrderID = 11088;

验证产品库存是否相应调整

select * from Products where ProductID = 18;

3 撤销订单

客户ID为ALFKI的客户撤销一个订单（订单号为11088）

业务逻辑    

删除相关记录

1）在Orders表中删除记录

delete from Orders where OrderID=11088 and CustomerID='ALFKI';

2）在OrderDetails做级联删除

delete from OrderDetails where OrderID=11088;

3）在products表中必须恢复相关产品的库存。

update Products set  UnitsInStock=  UnitsInStock + 10 where ProductID=13;  update Products set  UnitsInStock=  UnitsInStock + 8 where ProductID=14;  update Products set  UnitsInStock=  UnitsInStock + 5 where ProductID=18;

 触发器设计在Orders上建立删除触发器 -实现级联删除！

Create trigger deletcscd after delete  on Orders for each row delete from OrderDetails where OrderID=old.OrderID;

根据上述要求，设计测试用例，测试实际效果。

1) 验证在Orders表中删除记录

delete from Orders where OrderID = 11088 and CustomerID = 'ALFKI';

2) 验证在OrderDetails做级联删除

select * from OrderDetails where OrderID = 11088;

3) 验证在Products表中恢复相关产品的库存select * from Products where ProductID in (13, 14, 18);

以上测试用例将验证：订单号为11088的记录是否被从Orders表中成功删除。

是否从OrderDetails表中级联删除了与订单号为11088相关的记录。

相关产品（产品ID为13、14和18）的库存数量是否正确地增加回原始值。

五、实验截图