MySQL触发器触发表不存在异常，求实现部门员工数不超8人的触发器

Hey there! Let's fix up your MySQL trigger issue step by step, addressing both the "table doesn't exist" error and the department employee limit logic.

先搞定“表不存在”的潜在诱因 #

The "table doesn't exist" error you're hitting is almost certainly due to inconsistent table name capitalization:

• You defined the trigger to run on EMPLOYEE (uppercase with backticks), but your subquery references Employee (only first letter capitalized).
• MySQL is case-sensitive with table names on Linux/Unix systems (default behavior), but not on Windows. To avoid this cross-environment headache, stick to consistent capitalization and wrap table/column names in backticks consistently.

修正后的完整触发器代码（实现部门员工≤8人） #

Your original logic had a small flaw: if a department already has 8 employees, inserting a new one would push it to 9—so we need to block the insert when the current count reaches 8, not when it hits 9. Here's the polished code:

DELIMITER //
CREATE TRIGGER ensure_dept_count_on_insert
BEFORE INSERT ON `EMPLOYEE`
FOR EACH ROW
BEGIN
    DECLARE current_emp_count INT;
    -- 统一用反引号包裹表名和字段，彻底避免大小写识别问题
    SET current_emp_count = (SELECT COUNT(*) FROM `EMPLOYEE` WHERE `Dno` = NEW.`Dno`);
    
    -- 当前部门员工数已达上限时，阻止插入操作
    IF current_emp_count >= 8 THEN
        SIGNAL SQLSTATE '45000' -- 用45000（用户自定义异常码）更合适，02000是“无数据”状态码，语义不符
        SET MESSAGE_TEXT = '每个部门的员工数量不能超过8人';
    END IF;
END //
DELIMITER ;

关键细节说明 #

• SQLSTATE选择：Your initial 02000 code is for "no data found" scenarios. 45000 is the standard user-defined exception code, which makes your error message more semantically correct.
• 表名一致性：Using backticks for EMPLOYEE and Dno everywhere ensures the database recognizes them correctly, regardless of OS.
• 逻辑修正：Swapping maxCount = 9 to current_emp_count >=8 stops the insert before the department exceeds the 8-employee limit.

额外优化：处理员工调岗场景 #

If you also need to prevent employees from being transferred into a department that's already at capacity, add this BEFORE UPDATE trigger:

DELIMITER //
CREATE TRIGGER ensure_dept_count_on_update
BEFORE UPDATE ON `EMPLOYEE`
FOR EACH ROW
BEGIN
    DECLARE target_dept_count INT;
    -- 仅当员工部门发生变更时才检查目标部门人数
    IF OLD.`Dno` != NEW.`Dno` THEN
        SET target_dept_count = (SELECT COUNT(*) FROM `EMPLOYEE` WHERE `Dno` = NEW.`Dno`);
        IF target_dept_count >=8 THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '目标部门员工数量已达上限，无法调整该员工部门';
        END IF;
    END IF;
END //
DELIMITER ;

内容的提问来源于stack exchange，提问作者Bishoy N. Gendy