创建TRIGGER自动按员工统计各休假类型总数的技术需求

首先明确两张表的结构（假设明细表名为EmployeeLeave，汇总表名为LeaveSummary）：

1. 员工休假明细表 #

2. 目标休假汇总表 #

期望汇总结果：

触发器实现（分数据库版本） #

SQL Server 版本

该触发器会在明细表发生INSERT/UPDATE/DELETE操作时，自动重新计算受影响员工的休假统计，并同步到汇总表（存在则更新，不存在则插入）：

CREATE TRIGGER trg_UpdateLeaveSummary
ON EmployeeLeave
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 计算受影响员工的休假统计
    WITH EmployeeLeaveStats AS (
        SELECT
            EmployeeID,
            SUM(CASE WHEN LeaveTypeID = 1 THEN 1 ELSE 0 END) AS Type1,
            SUM(CASE WHEN LeaveTypeID = 2 THEN 1 ELSE 0 END) AS Type2
        FROM EmployeeLeave
        WHERE EmployeeID IN (
            SELECT EmployeeID FROM INSERTED
            UNION
            SELECT EmployeeID FROM DELETED
        )
        GROUP BY EmployeeID
    )
    -- 同步到汇总表
    MERGE INTO LeaveSummary AS Target
    USING EmployeeLeaveStats AS Source
        ON Target.EmployeeID = Source.EmployeeID
    WHEN MATCHED THEN
        UPDATE SET
            Target.Type1 = Source.Type1,
            Target.Type2 = Source.Type2
    WHEN NOT MATCHED THEN
        INSERT (EmployeeID, Type1, Type2)
        VALUES (Source.EmployeeID, Source.Type1, Source.Type2);
END;

MySQL 版本

MySQL需要针对不同操作创建触发器（8.0+版本可合并为一个触发器）：

处理INSERT操作的触发器

DELIMITER //
CREATE TRIGGER trg_LeaveSummary_AfterInsert
AFTER INSERT ON EmployeeLeave
FOR EACH ROW
BEGIN
    -- 更新或插入对应员工的汇总记录
    INSERT INTO LeaveSummary (EmployeeID, Type1, Type2)
    SELECT 
        NEW.EmployeeID,
        SUM(CASE WHEN LeaveTypeID = 1 THEN 1 ELSE 0 END),
        SUM(CASE WHEN LeaveTypeID = 2 THEN 1 ELSE 0 END)
    FROM EmployeeLeave
    WHERE EmployeeID = NEW.EmployeeID
    GROUP BY EmployeeID
    ON DUPLICATE KEY UPDATE
        Type1 = VALUES(Type1),
        Type2 = VALUES(Type2);
END //
DELIMITER ;

处理UPDATE/DELETE操作的触发器

DELIMITER //
CREATE TRIGGER trg_LeaveSummary_AfterUpdateDelete
AFTER UPDATE, DELETE ON EmployeeLeave
FOR EACH ROW
BEGIN
    -- 获取受影响的员工ID（DELETE时用OLD，UPDATE时用NEW/OLD均可）
    SET @emp_id = COALESCE(NEW.EmployeeID, OLD.EmployeeID);
    
    -- 更新对应员工的汇总记录
    INSERT INTO LeaveSummary (EmployeeID, Type1, Type2)
    SELECT 
        @emp_id,
        SUM(CASE WHEN LeaveTypeID = 1 THEN 1 ELSE 0 END),
        SUM(CASE WHEN LeaveTypeID = 2 THEN 1 ELSE 0 END)
    FROM EmployeeLeave
    WHERE EmployeeID = @emp_id
    GROUP BY EmployeeID
    ON DUPLICATE KEY UPDATE
        Type1 = VALUES(Type1),
        Type2 = VALUES(Type2);
END //
DELIMITER ;

注意事项 #

• 确保LeaveSummary表的EmployeeID字段设置为主键或唯一约束，避免重复记录
• 触发器仅针对受影响的员工重新计算统计，避免全表扫描提升性能
• 如果需要支持更多休假类型，只需扩展CASE语句即可

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