实验报告4：触发器的使用

Question

一、实验目的：
掌握触发器的使用方法。
二、实验任务：
1．在员工信息表（Employees）上创建一个触发器，每次插入一个职工记录时，将保存职工人数的变量@num（初始值为10）的值加1。
2．在员工薪水信息表（Salary）上面创建一个触发器，要求插的薪水信息收入值在1500-3500之间。如果插入的记录不满足条件，则在插入前将记录的“收入”字段值修改为1500。
3．在员工信息表（Employees）上创建一个触发器，要求在修改职工的出生日期时，不能修改为当前超过60岁的出生日期，如果不满足条件，则不允许修改。
4．将员工信息表（Employees）上的外键删除，创建一个触发器，要求员工信息表（Employees）部门号的取值要满足参照完整性约束。
5．创建一个“按性别统计员工情况”表，表结构为：按性别统计员工情况（性别，人数，平均年龄）。然后在员工信息表（Employees）上创建一个触发器，当表中插入一条记录时，在“按性别统计员工情况”表中将对应性别记录中的人数和平均年龄字段的值进行更新。（注意：由于同一张表上同类型触发器只能有一个，所以需要先删除第1题的触发器）
三、实验过程及实验步骤：（要求写出每个实验题目的步骤以及所使用的命令，并附上必要的截图）

Answer 1

本答案参考GPT。若问题解决，望采纳；如仍有疑问，请评论回复。

以下是针对每个实验任务的SQL语句实现和步骤说明：

一、实验目的：

掌握触发器的使用方法。

二、实验任务：

1. 在员工信息表（Employees）上创建一个触发器，每次插入一个职工记录时，将保存职工人数的变量@num（初始值为10）的值加1。

-- 假设Employees表结构为：EmployeeID, EmployeeName, DepartmentID等
-- 假设有一个变量@num用于保存职工人数，初始值为10

-- 首先设置初始值
SET @num = 10;

-- 创建触发器
DELIMITER //
CREATE TRIGGER AfterInsertEmployee
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    SET @num = @num + 1;
END //
DELIMITER ;

2. 在员工薪水信息表（Salary）上面创建一个触发器，要求插入的薪水信息收入值在1500-3500之间。如果插入的记录不满足条件，则在插入前将记录的“收入”字段值修改为1500。

-- 假设Salary表结构为：EmployeeID, Salary等

DELIMITER //
CREATE TRIGGER BeforeInsertSalary
BEFORE INSERT ON Salary
FOR EACH ROW
BEGIN
    IF NEW.Salary < 1500 OR NEW.Salary > 3500 THEN
        SET NEW.Salary = 1500;
    END IF;
END //
DELIMITER ;

3. 在员工信息表（Employees）上创建一个触发器，要求在修改职工的出生日期时，不能修改为当前超过60岁的出生日期，如果不满足条件，则不允许修改。

-- 假设Employees表结构中包含BirthDate字段

DELIMITER //
CREATE TRIGGER BeforeUpdateEmployee
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF YEAR(CURDATE()) - YEAR(NEW.BirthDate) > 60 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update to a birth date that makes the employee over 60 years old.';
    END IF;
END //
DELIMITER ;

4. 将员工信息表（Employees）上的外键删除，创建一个触发器，要求员工信息表（Employees）部门号的取值要满足参照完整性约束。

-- 首先删除外键约束，假设外键名为FK_DepartmentID

ALTER TABLE Employees DROP FOREIGN KEY FK_DepartmentID;

-- 创建触发器以检查部门号的参照完整性
DELIMITER //
CREATE TRIGGER CheckDepartmentID
BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
    DECLARE deptCount INT;
    SELECT COUNT(*) INTO deptCount FROM Departments WHERE DepartmentID = NEW.DepartmentID;
    IF deptCount = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid DepartmentID, does not exist in Departments table.';
    END IF;
END //
DELIMITER ;

5. 创建一个“按性别统计员工情况”表，表结构为：按性别统计员工情况（性别，人数，平均年龄）。然后在员工信息表（Employees）上创建一个触发器，当表中插入一条记录时，在“按性别统计员工情况”表中将对应性别记录中的人数和平均年龄字段的值进行更新。（注意：由于同一张表上同类型触发器只能有一个，所以需要先删除第1题的触发器）

-- 创建按性别统计员工情况表
CREATE TABLE GenderStats (
    Gender CHAR(1),
    Count INT,
    AverageAge FLOAT,
    PRIMARY KEY (Gender)
);

-- 删除第1题的触发器
DROP TRIGGER AfterInsertEmployee;

-- 创建触发器更新按性别统计员工情况
DELIMITER //
CREATE TRIGGER AfterInsertEmployeeStats
AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
    DECLARE currentCount INT;
    DECLARE currentAvgAge FLOAT;
    DECLARE newAvgAge FLOAT;
    DECLARE totalAge FLOAT;
    DECLARE totalCount INT;

    SELECT COUNT(*), AVG(BirthDate) INTO currentCount, currentAvgAge FROM Employees WHERE Gender = NEW.Gender;
    SET totalAge = currentAvgAge * currentCount + YEAR(CURDATE()) - YEAR(NEW.BirthDate);
    SET totalCount = currentCount + 1;
    SET newAvgAge = totalAge / totalCount;

    UPDATE GenderStats SET Count = totalCount, AverageAge = newAvgAge WHERE Gender = NEW.Gender;
    IF ROW_COUNT() = 0 THEN
        INSERT INTO GenderStats (Gender, Count, AverageAge) VALUES (NEW.Gender, 1, YEAR(CURDATE()) - YEAR(NEW.BirthDate));
    END IF;
END //
DELIMITER ;

三、实验过程及实验步骤：

1. 创建触发器：按照上述SQL语句创建触发器。
2. 测试触发器：插入、更新或删除数据以测试触发器是否按预期工作。
3. 截图：在每个步骤中，如果可能的话，截图数据库操作的结果，以证明触发器的功能。

请注意，上述SQL语句需要根据实际的数据库表结构和字段名称进行调整。此外，确保在执行这些操作之前，你有足够的权限来创建触发器和修改表结构。