Lab 7
EX1
Create a trigger that, when a new department is inserted into the department table, automatically assigns a temporary manager to that department in the management table.
DELIMITER//
CREATE TRIGGER ex1
AFTER INSERT ON department
FOR EACH ROW
BEGIN
-- 随机选取一个manager
DECLARE new_head INT;
SET new_head = (SELECT head_ID FROM head ORDER BY RAND() LIMIT 1);
INSERT INTO management VALUES(new.Department_ID, new_head, 'Yes');
END//
DELIMITER;
INSERT INTO department VALUES(16, 'Test name', 2023, 16, 0, 0);
执行插入语句后,department表如下:
![image-20231205130915900](https://img-blog.csdnimg.cn/img_convert/c15f76cde60e0458e4bcf09083c0568e.png)
management表如下:
![image-20231205131003310](https://img-blog.csdnimg.cn/img_convert/e6efcc6a0818471668ec627e554a4c43.png)
EX2
Create a trigger that, when the age of a manager is updated in the head table, if the age exceeds 60, sets them as a non-temporary manager in the management table.
DELIMITER//
CREATE TRIGGER ex2
AFTER UPDATE ON head
FOR EACH ROW
BEGIN
-- 需要确保management表中存在该head才能更新
IF(new.age > 60 AND EXISTS(SELECT * FROM management
WHERE management.head_ID = new.head_ID))
THEN
UPDATE management
SET temporary_acting = 'No'
WHERE management.head_ID = new.head_ID;
END IF;
END//
DELIMITER;
UPDATE head SET age = 99 WHERE head_ID = 9;
EX3
Create a trigger that, when a department is deleted from the department table, automatically deletes all management records for that department in the management table.
DELIMITER//
CREATE TRIGGER ex3
BEFORE DELETE ON department -- 由于management表的外键约束,需要用before
FOR EACH ROW
BEGIN
DELETE FROM management
WHERE management.department_ID = old.Department_ID;
END//
DELIMITER;
DELETE FROM department WHERE Department_ID = 16;
EX4
After inserting a record into the management table, if the “temporary_acting” field is ‘Yes’, automatically reduce the “Budget_in_Billions” field for the corresponding department by 10%. Create a trigger to implement this functionality.
DELIMITER//
CREATE TRIGGER ex4
AFTER INSERT ON management
FOR EACH ROW
BEGIN
IF(new.temporary_acting = 'Yes') THEN
UPDATE department
SET Budget_in_Billions = 0.9 * Budget_in_Billions
WHERE new.department_ID = Department_ID;
END IF;
END//
DELIMITER;
INSERT INTO management VALUES(1, 1, 'Yes');
![image-20231205134932966](https://img-blog.csdnimg.cn/img_convert/d23d557ef6edc947004704c237ae161c.png)
![image-20231205135117265](https://img-blog.csdnimg.cn/img_convert/8adfbcacd774f44891ad868020bdf500.png)
EX5
After inserting a record into the department table, automatically update the “Creation” field for that department to the current date. Create a trigger to implement this functionality.
DROP TRIGGER ex1;
DROP TRIGGER ex2;
DROP TRIGGER ex3;
DROP TRIGGER ex4;
DELIMITER//
CREATE TRIGGER ex5
BEFORE INSERT ON department
FOR EACH ROW
BEGIN
SET new.Creation = CURDATE();
END//
DELIMITER;
INSERT INTO department VALUES(16, 'Test name', 2000, 16, 0, 0);
几个注意事项:
- 需要使用
before
,因为after
触发器不能更新NEW
值 - 不能使用
update
语句来更新表,因为在触发器中对触发器表直接进行修改会导致死锁和递归问题;因此使用set
语句更新值 - 在执行本段代码前需要删除
ex1
触发器,否则报错:Can’t update table ‘department’ in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
因为ex1中在插入department后需要插入management表,而ex5中在插入department后需要修改department,这可能引起一致性问题,mysql会阻止这样的操作
![image-20231205142051568](https://img-blog.csdnimg.cn/img_convert/0fd3c1ae957041bc8db985cfd7da75a1.png)