数据库实验四：触发器实验实验四触发器实验

130 阅读 0 评论 86 点赞

我是靠谱客的博主傻傻书本，最近开发中收集的这篇文章主要介绍数据库实验四：触发器实验实验四触发器实验，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

实验四触发器实验

1.实验目的

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

2.实验内容和要求

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

3.实验重点和难点

实验重点：触发器的定义。

实验难点：利用触发器实现较为复杂的用户自定义完整性。

4.实验过程

创建一个教材信息表，教材订购表，开支表，一个选课统计表用于实验。

/*教材信息表*/
CREATE TABLE textbook(
bookno CHAR(10) PRIMARY KEY,
bookname CHAR(20),
cno char(9) REFERENCES Course(Cno),
price SMALLINT,
/*单价*/
discount_quantity SMALLINT,
/*折扣数量，超过该数量的订单可以使用折扣价格*/
discount FLOAT
/*折扣*/
);
/*教材订购表*/
CREATE TABLE textbook_order(
order_idx CHAR(10) PRIMARY KEY,
bookno CHAR(10),
num SMALLINT,
/*订购数量*/
total_price SMALLINT,
/*总价*/
order_status CHAR(10)	DEFAULT "未确认",
/*订单状态*/
CHECK(order_status IN ("未确认","已确认"))
);
/*开支表*/
CREATE TABLE expenditure(
expenditure_type CHAR(20),
amount INT,
creater_no CHAR(10),
order_status CHAR(20)
);
/*选课统计表*/
CREATE TABLE sc_summary(
Cno char(9),
cyear YEAR,
term CHAR(2),
snum SMALLINT,
/*已选人数*/
capacity SMALLINT,
/*课程容量*/
PRIMARY KEY(cno,cyear,term)
);

(1)AFTER触发器

在textbook表上定义一个UPDATE触发器，当修改教材价格，折扣数量，折扣时，修改教材订购表。在教材订购表上定义一个UPDATE触发器，修改教材订购表后修改开支表，保持数据一致性。

/*修改教材订购表的触发器*/
DELIMITER $
CREATE TRIGGER after_textbook_update
AFTER UPDATE ON textbook
FOR EACH ROW
BEGIN
/*如果编号发生了修改，订购表中的教材编号也要修改*/
UPDATE textbook_order
SET bookno = new.bookno
WHERE bookno = old.bookno;
/*计算正确的订单价格*/
UPDATE textbook_order
SET total_price = num*new.discount*new.price
WHERE bookno=new.bookno AND new.discount_quantity<=num;
UPDATE textbook_order
SET total_price = num*new.price
WHERE bookno=new.bookno AND new.discount_quantity>num;
END$
DELIMITER ;
/*修改开支表的触发器*/
DELIMITER $
CREATE TRIGGER after_textbook_order_update
AFTER UPDATE ON textbook_order
FOR EACH ROW
BEGIN
UPDATE expenditure
SET amount = amount-old.total_price+new.total_price
WHERE expenditure_type = "textbook";
END$
DELIMITER ;

在教材订购表上添加一个INSERT触发器，当增加一个教材订购订单表时，修改开支表中教材开支的费用，以保持数据的一致性。

DELIMITER $
CREATE TRIGGER after_insert_textbook_order
AFTER INSERT ON textbook_order
FOR EACH ROW
BEGIN
UPDATE expenditure
SET amount = amount+new.total_price
WHERE expenditure_type="textbook";
END$
DELIMITER ;

在教材订购表上添加一个DELETE触发器，当教材订购表删除一个订单时，删除开支中的费用，保持数据的一致性。

DELIMITER $
CREATE TRIGGER after_delete_textbook_order
AFTER DELETE ON textbook_order
FOR EACH ROW
BEGIN
UPDATE expenditure
SET amount = amount-old.total_price
WHERE expenditure_type = "textbook";
END$
DELIMITER ;

验证after_textbook_update，修改一本书的折扣。

/*插入一本教材和相应订单*/
INSERT INTO textbook
VALUES("2105","5",30,150,0.85,"数据库原理");
INSERT INTO textbook_order
VALUES("1","2105","200",5100,"未确认");
select * from textbook_order;

在这里插入图片描述

/*修改折扣*/
SET SQL_SAFE_UPDATES = 0;
UPDATE textbook
SET discount=0.82
WHERE bookno="2105";
SELECT * FROM textbook_order;
SELECT * FROM expenditure;

在这里插入图片描述

修改折扣后，教材订购表中的总金额重新进行了计算，开支表中的数据也相应修改。

(2)BEFORE触发器

在教材订购表上定义一个BEFORE UPDATE触发器，当修改订单时，如果订单的状态是"已确认"，则拒绝修改。

DELIMITER $
CREATE TRIGGER before_update_textbook_order
BEFORE UPDATE ON textbook_order
FOR EACH ROW
BEGIN
IF(old.order_status="已确认") THEN
SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = '该订单已确认，不可修改';
END IF;
END$
DELIMITER ;

在选课表上定义一个INSERT触发器，当插入一条选课记录时，先查询选课统计表中是否还有剩余的选课容量，有剩余的容量才可以插入该选课记录，并更新选课统计表中的值。

DELIMITER $
CREATE TRIGGER before_insert_sc
BEFORE INSERT ON sc
FOR EACH ROW
BEGIN
DECLARE available_capacity SMALLINT;
SELECT capacity-snum INTO available_capacity
FROM sc_summary;
IF(available_capacity>0) THEN
UPDATE sc_summary
SET snum = snum + 1
where cno = new.cno;
ELSE
SIGNAL SQLSTATE 'TX000' SET MESSAGE_TEXT = '该课程已无剩余容量！';
END IF;
END$
DELIMITER ;

在选课表上定义一个DELETE触发器，当删除一个选课记录时，将相应的选课统计表中的选课人数-1。

DELIMITER $
CREATE TRIGGER before_delete_sc
BEFORE DELETE ON sc
FOR EACH ROW
BEGIN
UPDATE sc_summary
SET snum = snum-1
WHERE cno = old.cno;
END$
DELIMITER ;

验证before_update_text_book触发器，修改状态为"已确认"的订单。

/*将订单1修改为已确认*/
UPDATE textbook_order
SET order_status="已确认"
WHERE order_idx="1";
/*修改该订单*/
UPDATE textbook_order
SET num=300
WHERE order_idx="1";

在这里插入图片描述

验证before_insert_sc触发器，对一个无容量的课程插入选课记录。

/*插入一个课程，容量为0*/
INSERT INTO sc_summary
VALUES("5","2022","1",0,0);
/*插入一个选课记录*/
INSERT INTO sc
VALUES("202004152","5",NULL);

在这里插入图片描述

(3)删除触发器

删除触发器after_textbook_update。

DROP TRIGGER after_textbook_update;

5.实验总结

触发器类似于约束，但能实现更复杂的检查和操作，尤其是可以在动作体中对其他表或数据库对象进行修改。使用BEFORE触发器在MYSQL中可以通过SIGNAL语句抛出异常，拒绝执行。

6.思考题

设计一个AFTER触发器，当Lineitem表中的quantity变化时，自动计算Lineitem中的extendedprice值，同时也要修改PartSupp中的availqty值。

实现如下：

DELIMITER $
CREATE TRIGGER after_update_lineitem
AFTER UPDATE lineitem
FOR EACH ROW
BEGIN
DECLARE rp REAL;
SELECT retailprice INTO rp
FROM Part
WHERE new.partkey = Part.partkey;
/*重新计算extendedprice*/
UPDATE lineitem
SET extendedprice =
new.quantity*rp
WHERE partkey = new.partkey AND suppkey=new.suppkey;
/*更新PartSupp中的availqty*/
UPDATE PartSupp
SET availqty = availqty+old.quantity-new.quantity
WHERE partkey = new.partkey AND suppkey=new.suppkey;
END$
DELIMITER ;