实验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触发器。实验过程中,由于基础知识薄弱与实践经验匮乏,遇到了不少难题,好在有老师和同学的帮助克服了困难。经过实践,更激励了我们继续努力学习,打好基础。