PostgreSQL使用触发器在employee表增删时更新department表dcount值

错误原因排查 #

你之前的代码出现全表dcount统一变更的核心问题有两点：

• 触发逻辑中UPDATE department语句未添加WHERE dno = 目标部门编号过滤条件，导致全表的dcount都被同步修改
• 未正确绑定操作行的部门编号：插入操作要取新插入行的NEW.dno，删除操作要取被删除行的OLD.dno

实现代码 #

PostgreSQL 版本 #

首先创建触发函数：

CREATE OR REPLACE FUNCTION update_dept_dcount()
RETURNS TRIGGER AS $$
BEGIN
    -- 处理员工插入操作
    IF TG_OP = 'INSERT' THEN
        UPDATE department 
        SET dcount = dcount + 1 
        WHERE dno = NEW.dno;
        RETURN NEW;
    -- 处理员工删除操作
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE department 
        SET dcount = dcount - 1 
        WHERE dno = OLD.dno;
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql;

再绑定触发器到employee表：

CREATE TRIGGER trg_employee_dcount_change
AFTER INSERT OR DELETE ON employee
FOR EACH ROW EXECUTE FUNCTION update_dept_dcount();

MySQL 版本 #

分别创建插入和删除触发器：

插入触发器

DELIMITER //
CREATE TRIGGER trg_employee_insert AFTER INSERT ON employee
FOR EACH ROW
BEGIN
    UPDATE department SET dcount = dcount + 1 WHERE dno = NEW.dno;
END//
DELIMITER ;

删除触发器

DELIMITER //
CREATE TRIGGER trg_employee_delete AFTER DELETE ON employee
FOR EACH ROW
BEGIN
    UPDATE department SET dcount = dcount - 1 WHERE dno = OLD.dno;
END//
DELIMITER ;

补充注意事项 #

• 必须使用行级触发器（FOR EACH ROW），才能逐行匹配员工对应的部门更新人数，不要使用语句级触发器
• 触发器部署前建议先初始化各部门的现有员工数，保证初始统计值准确：

UPDATE department d 
SET dcount = (SELECT COUNT(*) FROM employee e WHERE e.dno = d.dno);

• 如果需要支持员工调部门（即employee表dno字段更新）的场景，可以额外增加UPDATE操作的判断逻辑，将旧部门人数减1，新部门人数加1即可。

内容的提问来源于stack exchange，提问作者Sai Gruheeth