数据库实验八 触发器实验

1.实验目的

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

   2.实验内容和要求

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

3.实验步骤

1. 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表：