实验报告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。

DELIMITER //
CREATE TRIGGER IncreaseEmployeeCount BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
  SET @num = @num + 1;
END; //
DELIMITER ;

2. 在员工薪水信息表（Salary）上面创建一个触发器

要求插入的薪水信息收入值在1500-3500之间。如果插入的记录不满足条件，则在插入前将记录的“收入”字段值修改为1500。

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

3. 在员工信息表（Employees）上创建一个触发器

要求在修改职工的出生日期时，不能修改为当前超过60岁的出生日期，如果不满足条件，则不允许修改。

DELIMITER //
CREATE TRIGGER CheckAgeBeforeUpdate BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
  IF NEW.出生日期 < DATE_SUB(CURDATE(), INTERVAL 60 YEAR) THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot modify to an age over 60.';
  END IF;
END; //
DELIMITER ;

4. 将员工信息表（Employees）上的外键删除，创建一个触发器

要求员工信息表（Employees）部门号的取值要满足参照完整性约束。

首先删除外键：

ALTER TABLE Employees DROP FOREIGN KEY fk_department;

然后创建触发器：

DELIMITER //
CREATE TRIGGER CheckDepartmentBeforeInsert BEFORE INSERT ON Employees
FOR EACH ROW
BEGIN
  DECLARE dept_count INT;
  SELECT COUNT(*) INTO dept_count FROM Departments WHERE 部门号 = NEW.部门号;
  IF dept_count = 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid department number.';
  END IF;
END; //
DELIMITER ;

5. 创建一个“按性别统计员工情况”表，并在员工信息表（Employees）上创建一个触发器

当表中插入一条记录时，在“按性别统计员工情况”表中将对应性别记录中的人数和平均年龄字段的值进行更新。

首先创建统计表：

CREATE TABLE 按性别统计员工情况 (
  性别 CHAR(1),
  人数 INT,
  平均年龄 FLOAT
);

然后删除第1题的触发器（如果存在）：

DROP TRIGGER IF EXISTS IncreaseEmployeeCount;

最后创建触发器：

DELIMITER //
CREATE TRIGGER UpdateGenderStats AFTER INSERT ON Employees
FOR EACH ROW
BEGIN
  DECLARE existing_count INT;
  DECLARE existing_avg_age FLOAT;
  DECLARE new_avg_age FLOAT;
  
  SELECT 人数, 平均年龄 INTO existing_count, existing_avg_age FROM 按性别统计员工情况 WHERE 性别 = NEW.性别;
  
  IF existing_count IS NULL THEN
    INSERT INTO 按性别统计员工情况 (性别, 人数, 平均年龄) VALUES (NEW.性别, 1, DATEDIFF(CURDATE(), NEW.出生日期) / 365);
  ELSE
    SET new_avg_age = (existing_avg_age * existing_count + DATEDIFF(CURDATE(), NEW.出生日期) / 365) / (existing_count + 1);
    UPDATE 按性别统计员工情况 SET 人数 = existing_count + 1, 平均年龄 = new_avg_age WHERE 性别 = NEW.性别;
  END IF;
END; //
DELIMITER ;

三、实验过程及实验步骤

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

请注意，上述SQL语句需要根据您的具体数据库环境（如MySQL、SQL Server等）进行适当的调整。此外，确保在执行这些操作之前备份您的数据库，以防万一。