测试自定义MySQL触发器时触发Error Code:2014错误，求排查建议

直接触发Error 2014的核心问题及修复 #

Error Code 2014在触发器场景下，大多源于SELECT INTO语句返回多行结果，或无返回结果时未处理异常，结合你的代码逐一梳理：

1. 修复表名拼写错误 #

代码中存在明显拼写错误：acq_training_coruse_session应为acq_training_course_session（coruse→course），该错误会直接导致对应SELECT语句执行失败，触发异常。

2. 给SELECT INTO添加WHERE条件，确保返回单行结果 #

• 原代码中SELECT max_number_of_attendees INTO max_number_of_enrolment FROM acq_training_course_session;无WHERE条件，当表中有多个课程会话时会返回多行数据，SELECT INTO无法处理，必须限定为当前要插入的课程：

  SELECT max_number_of_attendees INTO max_number_of_enrolment 
  FROM acq_training_course_session 
  WHERE training_course_session_id = NEW.course_id;
  

• 原代码中SELECT count(training_enrolment_id) INTO current_number_of_enrolment FROM acq_training_enrolment;无WHERE条件，统计的是全表报名数，应改为统计当前课程的报名数：

  SELECT count(training_enrolment_id) INTO current_number_of_enrolment 
  FROM acq_training_enrolment 
  WHERE course_id = NEW.course_id;
  

3. 修正逻辑冗余与错误条件 #

• temp_staff_id != NEW.staff_id逻辑冗余：查询条件是acq_person_id = NEW.staff_id，只要查询到结果，temp_staff_id必然等于NEW.staff_id；若查询不到会直接抛出NO DATA FOUND异常，该条件可删除。
• temp_course_id != NEW.course_id同理，且表名已修正，该条件也可删除。
• NEW.staffID大小写错误：统一改为NEW.staff_id，避免大小写敏感的数据库中字段匹配失败。

4. 处理SELECT INTO无结果的异常 #

若NEW.staff_id不存在于acq_staff，或NEW.course_id不存在于acq_training_course_session，对应的SELECT INTO会无结果返回，触发异常。可添加异常处理：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET msg = 'Invalid staff or course ID';

将其放在DECLARE变量之后、BEGIN块内的开头位置。

修正后的触发器示例 #

DELIMITER //
create trigger training_session_is_full 
    before insert on acq_training_enrolment
    for each row
begin

    DECLARE max_number_of_enrolment INT;
    DECLARE current_number_of_enrolment INT;    
    DECLARE temp_staff_id INT;
    DECLARE temp_course_id INT;
    DECLARE msg VARCHAR(255);
    
    -- 处理无结果返回的异常
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET msg = 'Invalid staff or course ID';
    
    SELECT acq_person_id INTO temp_staff_id FROM acq_staff WHERE acq_person_id = NEW.staff_id;
    SELECT training_course_session_id INTO temp_course_id FROM acq_training_course_session WHERE training_course_session_id = NEW.course_id;
    SELECT max_number_of_attendees INTO max_number_of_enrolment FROM acq_training_course_session WHERE training_course_session_id = NEW.course_id;
    SELECT count(training_enrolment_id) INTO current_number_of_enrolment FROM acq_training_enrolment WHERE course_id = NEW.course_id;

    IF(max_number_of_enrolment <= current_number_of_enrolment) OR 
       temp_staff_id IS NULL OR
       temp_course_id IS NULL OR
       EXISTS (SELECT 1 FROM acq_training_enrolment WHERE acq_staff_acq_person_id = NEW.staff_id AND course_id = NEW.course_id)
    THEN 
        SET msg = IF(msg IS NOT NULL, msg, 'Session is full or staff already enrolled');
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = msg;
    END IF;
    
end //
DELIMITER ;

额外注意事项 #

• 触发器定义时需使用DELIMITER修改分隔符，避免分号提前结束触发器定义（你的原始代码未添加，这是常见疏漏）。
• 判断用户是否已报名时，EXISTS比IN更高效，同时限定课程ID可避免其他课程的报名记录干扰当前判断。

内容的提问来源于stack exchange，提问作者심준호