[大数据] 数据库实验八

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 数据库实验八 -> 正文阅读

[大数据]数据库实验八

数据库实验八：触发器

0 基础知识回顾：

在这里插入图片描述

1 AFTER 触发器

1.1 在Lineitem 表上定义一个UPDATE 触发器，当修改订单明细（即修改订单明细价格extendedprice、折扣discount、税率tax）时，自动修改订单Orders 的TotalPrice，以保持数据一致性。

实验七的时候我就想着，订单明细价格和折扣税率的关系到底是什么样子的，当时想的是，订单明细价格是按linenumber划分的子订单的原价（订单用orderkey区分，订单明细（子订单）用orderkey和linenumber联合区分），然后需要再用tax和discount进行再加工，即需要把原价更新成extendedprice*（1+tax）*discount，然后用这个新的extendedprice去更新orders表的totalprice，现在看起来之前的这个想法好像不太对似的233333333（我也分不清到底应该怎么算了23333），如果计算方法有错干脆将错就错吧，懒得改了

这样的话我这里应该是默认old.extendedprice是已经被old.tax和old.discount处理过的

CREATE TRIGGER update_totalprice AFTER UPDATE ON lineitem
 FOR EACH ROW 
 BEGIN
 UPDATE orders SET totalprice = totalprice - old.extendedprice
  + new.extendedprice * new.discount * (1.00 + new.tax)
   WHERE orderkey = new.orderkey && orderkey = old.orderkey;
 END

1.2 在Lineitem 表上定义一个INSERT 触发器，当增加一项订单明细时，自动修改订单Orders 的TotalPrice，以保持数据的一致性。

CREATE TRIGGER insert_totalprice AFTER INSERT ON lineitem FOR EACH ROW 
 BEGIN
 UPDATE orders SET totalprice = totalprice + new.extendedprice * new.discount * (1.00 + new.tax)
  WHERE orderkey = new.orderkey;
 END

1.3 在Lineitem 表上定义一个DELETE 触发器，当删除一项订单明细时，自动修改订单Orders 的TotalPrice，以保持数据一致性。

CREATE TRIGGER delete_totalprice AFTER DELETE ON lineitem for each row
 BEGIN
 UPDATE orders SET totalprice = totalprice - old.extendedprice
  WHERE orderkey = old.orderkey;
 END

1.4 验证上面的三个触发器是否起作用。

SELECT totalprice FROM orders WHERE orderkey = 1012;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1012; 
UPDATE lineitem SET extendedprice = 200000 WHERE orderkey = 1012 && linenumber = 1;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1012; 
SELECT totalprice FROM orders WHERE orderkey = 1012;

SELECT totalprice FROM orders WHERE orderkey = 1013;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1013; 
INSERT INTO lineitem(orderkey,linenumber,extendedprice,discount,tax)  VALUES (1013,3,100000,0.85,0.23);
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1013; 
SELECT totalprice FROM orders WHERE orderkey = 1013;

SELECT totalprice FROM orders WHERE orderkey = 1013;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1013; 
DELETE FROM lineitem WHERE orderkey = 1013 && linenumber = 3;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1013; 
SELECT totalprice FROM orders WHERE orderkey = 1013;

update :
在这里插入图片描述

insert
在这里插入图片描述

delete
在这里插入图片描述

2 BEFORE 触发器

2.1 在Lineitem 表上定义一个BEFORE UPDATE 触发器，当修改订单明细中的数量（quantity）时，先检查供应表PartSupp 中的可用数量availqty 是否足够。

在这里插入图片描述

CREATE TRIGGER update_before BEFORE UPDATE ON lineitem FOR EACH ROW 
 BEGIN
  DECLARE availqty_now INT;
   SELECT availqty INTO availqty_now FROM partsupp WHERE partkey = new.partkey && suppkey = new.suppkey;
   IF availqty_now + old.quantity >=  new.quantity THEN
    UPDATE partsupp SET availqty = availqty_now - new.quantity + old.quantity WHERE partkey = new.partkey && suppkey = new.suppkey;
    ELSE
     SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'availqty is not enough';
   END IF;
  END

2.2 在Lineitem 表上定义一个BEFORE INSERT 触发器，当插入订单明细时，先检查供应表PartSupp 中的可用数量availqty 是否足够。

CREATE TRIGGER insert_before BEFORE INSERT ON lineitem FOR EACH ROW 
 BEGIN
  DECLARE availqty_now INT;
   SELECT availqty INTO availqty_now FROM partsupp WHERE partkey = new.partkey && suppkey = new.suppkey;
   IF availqty_now >= new.quantity THEN
    UPDATE partsupp SET availqty = availqty_now - new.quantity WHERE partkey = new.partkey && suppkey = new.suppkey;
    ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'availqty is not enough';
   END IF;
  END

2.3 在Lineitem 表上定义一个BEFORE DELETE 触发器，当删除订单明细时，该订单明细项订购的数量要归还于对应的零件供应记录。

CREATE TRIGGER delete_before BEFORE DELETE ON lineitem FOR EACH ROW 
 BEGIN
    UPDATE partsupp SET availqty = availqty + old.quantity WHERE partkey = old.partkey && suppkey = old.suppkey;
  END

在这里插入图片描述

2.4 验证上面的三个触发器是否起作用。

select partsupp.availqty,partsupp.partkey,partsupp.suppkey
 from partsupp,lineitem where lineitem.orderkey = 1016 && lineitem.linenumber = 2 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;
 update lineitem set quantity = 8000 where orderkey = 1016 && linenumber = 2;

 insert into lineitem(orderkey,partkey,suppkey,linenumber,quantity) values (1016,6440,21004,4,8000);
 
 insert into lineitem(orderkey,partkey,suppkey,linenumber,quantity) values (1016,6440,21004,4,80);
 select availqty,partkey,suppkey from partsupp where partkey = 6440 && suppkey =21004;
 select quantity,orderkey,linenumber,partkey,suppkey from lineitem where orderkey = 1016;
 delete from lineitem where orderkey = 1016 && linenumber = 4;
 select availqty from partsupp where partkey = 6440 && suppkey =21004;