数据库实验4 触发器实验

芜湖韩金轮

已于 2022-12-30 10:41:32 修改

阅读量3k

点赞数 4

分类专栏：数据库文章标签：数据库 sql mysql

于 2022-12-22 21:38:40 首次发布

本文链接：https://blog.csdn.net/qq_51684393/article/details/128413916

版权

数据库专栏收录该内容

17 篇文章 21 订阅

订阅专栏

实验4 触发器实验

1.实验目的

掌握数据库触发器的设计和使用方法。

2.实验内容和要求

定义BEFORE触发器和AFTER触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

3.实验过程

触发器（Trigger）是用户定义在关系表上的一类由事件驱动的特殊过程

由服务器自动激活
可以进行更为复杂的检查和操作，具有更精细和更强大的数据控制能力

CREATE TRIGGER语法格式

CREATE TRIGGER <触发器名>  
       {BEFORE | AFTER} <触发事件> ON <表名>
        FOR EACH  {ROW | STATEMENT}
      ［WHEN <触发条件>］
        <触发动作体>

定义触发器的语法说明:

创建者：表的拥有者
触发器名
表名：触发器的目标表
触发事件：INSERT、DELETE、UPDATE
触发器类型

行级触发器（FOR EACH ROW）

语句级触发器（FOR EACH STATEMENT）

例如,假设在［例11］的TEACHER表上创建了一个AFTER UPDATE触发器。如果表TEACHER有1000行，执行如下语句：

UPDATE TEACHER SET Deptno=5;

如果该触发器为语句级触发器，那么执行完该语句后，触发动作只发生一次
如果是行级触发器，触发动作将执行1000次

触发条件

触发条件为真

省略WHEN触发条件

触发动作体

触发动作体可以是一个匿名PL/SQL过程块

也可以是对已创建存储过程的调用

1.AFTER 触发器

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

订单明细修改后,先计算订单含税折扣价总价的修正值（新的减去旧的），再加上原价就是更新后的总价

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

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

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

原理同上

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

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

4）验证上面的三个触发器是否起作用

验证UPDATA

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

在这里插入图片描述

更新后结果:

经过计算，totalprice=15184+（200000-0）*（1-0.83）*（1+0.54）=67554，故UPDATA触发器起作用

验证INSERT

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

在这里插入图片描述

插入后结果如下：

在这里插入图片描述

经过计算，totalprice=42868+100000*（1-0.85）*（1+0.23）=61318，故INSERT触发器起作用

验证DELETE

SELECT totalprice FROM orders WHERE orderkey = 1005;
SELECT linenumber,extendedprice,discount,tax FROM lineitem WHERE orderkey = 1005; 
DELETE FROM lineitem WHERE orderkey = 1005 && linenumber = 3;
SELECT linenumber,extendedprice FROM lineitem WHERE orderkey = 1005; 
SELECT totalprice FROM orders WHERE orderkey = 1005;

在这里插入图片描述

删除后结果：

在这里插入图片描述

经过计算，totalprice=61318-100000*（1-0.85）*（1+0.23）=42868，故DELETE触发器起作用

2.BEFORE 触发器

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

#BEFORE UPDATE 触发器
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）在Lineitem 表上定义一个BEFORE INSERT 触发器，当插入订单明细时，先检查供应表PartSupp 中的可用数量availqty 是否足够。

#BEFORE INSERT 触发器
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

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

#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

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

验证UPDATE

#验证BEFORE UPDATE 触发器
SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 111 && lineitem.linenumber = 1 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

update lineitem 
set quantity = quantity+10 
where orderkey = 111 && linenumber = 1;

SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 111 && lineitem.linenumber = 1 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

在这里插入图片描述

若quantity更新的值过大的话，会报错

在这里插入图片描述

说明UPDATE触发器起作用

验证INSERT触发器

#验证BEFORE INSERT 触发器
SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 112 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

INSERT
INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity) 
values (112,6721,1595,2,100);

SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 112 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

在这里插入图片描述

若插入的元素的quantity过大，会报错
在这里插入图片描述

说明INSERT触发器起作用

验证DELETE触发器

SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 112 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

DELETE
FROM lineitem
WHERE orderkey = 112 && linenumber =2;

SELECT partsupp.partkey,partsupp.suppkey,partsupp.availqty,lineitem.quantity
FROM partsupp,lineitem 
WHERE lineitem.orderkey = 112 && partsupp.partkey = lineitem.partkey && partsupp.suppkey = lineitem.suppkey;

在这里插入图片描述

可以看到DELETE触发器起作用

4.思考题

试设计一个AFTER触发器,当Lineitem表中的quantity变化时，自动计算Lineitem表中的extendedprice值,同时也要修改PartSupp中的availqty值(提示: extendedprice = quantity *Part.retailprice)。

CREATE TRIGGER emp_update
AFTER UPDATE ON Lineitem
FOR EACH ROW
BEGIN
  IF(new.quantity!=old.quantity)
  THEN
    UPDATE Lineitem set Lineitem = quantity *Part.retailprice;
		UPDATE PartSupp set availqty=new.availqty;
  END IF;
END;

5.实验总结

通过本次实验，进一步了解了触发器的定义，初步掌握了数据库触发器的设计和使用方法，成功动手设置了BEFORE触发器和AFTER触发器。实验过程中，由于基础知识薄弱与实践经验匮乏，遇到了不少难题，好在有老师和同学的帮助克服了困难。经过实践，更激励了我们继续努力学习，打好基础。

芜湖韩金轮

关注

4
点赞
踩
49

收藏

觉得还不错? 一键收藏
0
评论
数据库实验4 触发器实验

通过本次实验，进一步了解了触发器的定义，初步掌握了数据库触发器的设计和使用方法，成功动手设置了BEFORE触发器和AFTER触发器。实验过程中，由于基础知识薄弱与实践经验匮乏，遇到了不少难题，好在有老师和同学的帮助克服了困难。经过实践，更激励了我们继续努力学习，打好基础。
复制链接

扫一扫

专栏目录