1.实验目的
掌握数据库触发器的设计和使用方法
2.实验内容和要求
定义 BEFORE 触发器和 AFTER 触发器。能够理解不同类型触发器的作用和执行原理,验证触发器的有效性。
3.实验步骤
-
AFTER 触发器
①在 Lineitem 表上定义一个 UPDATE 触发器,当修改订单明细(即修改订单明细价格 extendeprice、折扣 discount、税率 tax)时,自动修改订单 Orders 的 TotalPrice,以保持数据一致性。
源代码:
CREATE TRIGGER UP_lineitem 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;
END
②在 Lineitem 表上定义一个 INSERT 触发器,当增加一项订单明细时,自动修改订单 Orders 的 TotalPrice,以保持数据的一致性。
源代码:
CREATE TRIGGER IN_lineitem AFTER INSERT ON lineitem FOR EACH ROW
BEGIN
UPDATE orders
SET totalprice = new.extendedprice * (1 - new.discount) * (1 + new.tax)
WHERE
orderkey = new.orderkey;
END
③在 Lineitem 表上定义一个 DELETE 触发器,当删除一项订单明细时,自动修改订单 Orders 的 TotalPrice,以保持数据一致性。
源代码:
CREATE TRIGGER DE_lineitem 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
④验证上面的三个触发器是否起作用。
验证UPDATE触发器源代码:
SELECT
*
FROM
lineitem
WHERE
orderkey = 11;
SELECT
*
FROM
orders
WHERE
orderkey = 11;
UPDATE lineitem
SET tax = 0.16
WHERE
orderkey = 11;
SELECT
*
FROM
lineitem
WHERE
orderkey = 11;
SELECT
*
FROM
orders
WHERE
orderkey = 11;
验证UPDATE触发器结果截图:
修改前lineitem表:
修改前orders表:
修改后lineitem表:
修改后orders表:
验证INSERT触发器源代码:
SELECT
*
FROM
lineitem
WHERE
orderkey = 15;
SELECT
*
FROM
orders
WHERE
orderkey = 15;
INSERT INTO lineitem
VALUES
(
15,
5001,
15634,
1,
45,
95402,
0.10,
0.13,
'',
'',
NULL,
NULL,
NULL,
' ',
' ',
NULL
);
SELECT
*
FROM
lineitem
WHERE
orderkey = 15;
SELECT
*
FROM
orders
WHERE
orderkey = 15;
验证INSERT触发器结果截图:
插入前lineitem表:
插入前orders表:
插入后lineitem表:
插入后orders表:
验证DELETE触发器源代码:
SELECT
*
FROM
lineitem
WHERE
orderkey = 10;
SELECT
*
FROM
orders
WHERE
orderkey = 10;
DELETE
FROM
lineitem
WHERE
orderkey = 10
AND partkey = 20521;
SELECT
*
FROM
lineitem
WHERE
orderkey = 10;
SELECT
*
FROM
orders
WHERE
orderkey = 10;
验证DELETE触发器结果截图:
删除前lineitem表:
删除前orders表:
删除后lineitem表: 删除后orders表:
2.BEFORE 触发器
① 在 Lineitem 表上定义一个 BEFORE UPDATE 触发器, 当修改订单明细中的数量
(quantity)时,先检查供应表 PartSupp 中的可用数量 availqty 是否足够。
源代码:
CREATE TRIGGER BUP_lineitem BEFORE UPDATE ON lineitem FOR EACH ROW
BEGIN
SELECT
availqty INTO @UP_availqty
FROM
partsupp
WHERE
partkey = new.partkey
AND suppkey = new.suppkey;
IF (
@UP_availqty - (new.quantity - old.quantity) >= 0
) THEN
BEGIN
UPDATE partsupp
SET availqty = availqty - (new.quantity - old.quantity)
WHERE
partkey = new.partkey
AND suppkey = new.suppkey;
END;
END
IF;
END
②在 Lineitem 表上定义一个 BEFORE INSERT 触发器,当插入订单明细时,先检查供应表 PartSupp 中的可用数量 availqty 是否足够。
源代码:
CREATE TRIGGER BIN_lineitem BEFORE INSERT ON lineitem FOR EACH ROW
BEGIN
SELECT
availqty INTO @IN_availqty
FROM
partsupp
WHERE
partkey = new.partkey
AND suppkey = new.suppkey;
IF (
@IN_availqty - new.quantity >= 0
) THEN
BEGIN
UPDATE partsupp
SET availqty = availqty - (new.quantity)
WHERE
partkey = new.partkey
AND suppkey = new.suppkey;
END;
END
IF;
END
③在 Lineitem 表上定义一个 BEFORE DELETE 触发器,当删除订单明细时,该订单明细项订购的数量要归还于对应的零件供应记录。
源代码:
CREATE TRIGGER BDE_lineitem BEFORE DELETE ON lineitem FOR EACH ROW
BEGIN
UPDATE partsupp
SET availqty = availqty + (old.quantity)
WHERE
partkey = old.partkey
AND suppkey = old.suppkey;
END
④验证上面的三个触发器是否起作用。
验证UPDATE触发器源代码:
SELECT
*
FROM
lineitem
WHERE
partkey = 24941;
SELECT
*
FROM
partsupp
WHERE
partkey = 24941;
UPDATE lineitem
SET quantity = 6
WHERE
orderkey = 6
AND partkey = 24941;
SELECT
*
FROM
lineitem
WHERE
partkey = 24941;
SELECT
*
FROM
partsupp
WHERE
partkey = 24941;
验证UPDATE触发器结果截图:
更新前lineitem表:
更新前partsupp表:
更新后lineitem表:
更新后partsupp表:
验证INSERT触发器源代码:
SELECT
*
FROM
lineitem
WHERE
partkey = 44117;
SELECT
*
FROM
partsupp
WHERE
partkey = 44117;
INSERT INTO lineitem
VALUES
(
5,
44117,
533,
1,
12,
6666.00,
0.69,
0.39,
'',
'',
NULL,
NULL,
NULL,
' ',
' ',
NULL
);
SELECT
*
FROM
lineitem
WHERE
partkey = 44117;
SELECT
*
FROM
partsupp
WHERE
partkey = 44117;
验证INSERT触发器结果截图:
插入前lineitem表:
插入前partsupp表
插入后lineitem表:
插入后partsupp表:
验证DELETE触发器源代码:
SELECT
*
FROM
lineitem
WHERE
partkey = 44117;
SELECT
*
FROM
partsupp
WHERE
partkey = 44117;
DELETE
FROM
lineitem
WHERE
partkey = 44117
AND orderkey = 9;
SELECT
*
FROM
lineitem
WHERE
partkey = 44117;
SELECT
*
FROM
partsupp
WHERE
partkey = 44117;
验证DELETE触发器结果截图:
删除前lineitem表:
删除前partkey表:
删除后lineitem表:
删除后partkey表: