ECNU数据库作业——Lab7

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表如下：

management表如下：

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'); 

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);

几个注意事项：

1. 需要使用before，因为after触发器不能更新NEW值
2. 不能使用update语句来更新表，因为在触发器中对触发器表直接进行修改会导致死锁和递归问题；因此使用set语句更新值
3. 在执行本段代码前需要删除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会阻止这样的操作