PL/SQL触发器开发：基于students表同步维护speciality表

不用折腾存储过程啦，你要的逻辑直接在触发器里就能搞定！核心是利用Oracle触发器里的:new和:old伪记录来关联两张表，同步维护专业的总学分和学生数。我把完整的触发器代码写出来，再给你拆解每个部分的逻辑：

完整触发器代码 #

CREATE OR REPLACE TRIGGER UpdateSpeciality
AFTER INSERT OR DELETE OR UPDATE ON students
FOR EACH ROW
DECLARE
    v_student_count NUMBER;
BEGIN
    -- 处理插入学生的情况
    IF INSERTING THEN
        -- 先检查对应专业是否已经在speciality表中存在
        SELECT COUNT(*) INTO v_student_count
        FROM speciality
        WHERE major = :new.major;
        
        IF v_student_count > 0 THEN
            -- 专业已存在，更新总学生数和总学分
            UPDATE speciality
            SET total_students = total_students + 1,
                total_credits = total_credits + :new.current_credits
            WHERE major = :new.major;
        ELSE
            -- 专业不存在，插入一条新的专业统计记录
            INSERT INTO speciality (major, total_students, total_credits)
            VALUES (:new.major, 1, :new.current_credits);
        END IF;
    END IF;

    -- 处理删除学生的情况
    IF DELETING THEN
        -- 更新旧专业的统计数据：学生数减1，学分减去该学生的当前学分
        UPDATE speciality
        SET total_students = total_students - 1,
            total_credits = total_credits - :old.current_credits
        WHERE major = :old.major;
        
        -- 可选操作：如果该专业学生数变为0，删除这条专业记录
        DELETE FROM speciality
        WHERE major = :old.major AND total_students = 0;
    END IF;

    -- 处理更新学生的情况
    IF UPDATING THEN
        -- 子情况1：学生的专业没有变化，只需要调整总学分
        IF :new.major = :old.major THEN
            UPDATE speciality
            SET total_credits = total_credits + (:new.current_credits - :old.current_credits)
            WHERE major = :new.major;
        ELSE
            -- 子情况2：学生的专业变了，先处理旧专业的统计
            UPDATE speciality
            SET total_students = total_students - 1,
                total_credits = total_credits - :old.current_credits
            WHERE major = :old.major;
            
            -- 可选：旧专业学生数为0则删除
            DELETE FROM speciality
            WHERE major = :old.major AND total_students = 0;
            
            -- 再处理新专业的统计，逻辑和插入时一致
            SELECT COUNT(*) INTO v_student_count
            FROM speciality
            WHERE major = :new.major;
            
            IF v_student_count > 0 THEN
                UPDATE speciality
                SET total_students = total_students + 1,
                    total_credits = total_credits + :new.current_credits
                WHERE major = :new.major;
            ELSE
                INSERT INTO speciality (major, total_students, total_credits)
                VALUES (:new.major, 1, :new.current_credits);
            END IF;
        END IF;
    END IF;
END;
/

关键逻辑说明 #

• :new和:old伪记录：这是Oracle触发器的核心，用来获取操作前后的记录数据：
  • 插入操作只有:new，代表刚插入的学生记录
  • 删除操作只有:old，代表被删除的学生记录
  • 更新操作两者都有，:old是更新前的旧数据，:new是更新后的新数据
• 专业存在性判断：用SELECT COUNT(*)检查专业是否在speciality表中，避免重复插入或更新错误
• 可选的空专业清理：当某个专业的学生数变为0时，可以选择删除该专业的统计记录，你可以根据业务需求决定是否保留这部分逻辑

可选优化：用MERGE简化插入/更新逻辑 #

上面的插入和新专业处理部分，可以用Oracle的MERGE语句替代“判断存在性+插入/更新”的逻辑，让代码更简洁：

-- 替换插入操作里的判断+插入/更新代码
MERGE INTO speciality s
USING (SELECT :new.major AS major, :new.current_credits AS credits FROM dual) d
ON (s.major = d.major)
WHEN MATCHED THEN
    UPDATE SET total_students = total_students + 1, total_credits = total_credits + d.credits
WHEN NOT MATCHED THEN
    INSERT (major, total_students, total_credits) VALUES (d.major, 1, d.credits);

MERGE会自动判断目标表中是否存在匹配的专业，存在就更新，不存在就插入，省去了手动判断的步骤。

测试建议 #

你可以通过以下步骤验证触发器是否正常工作：

1. 插入一条students记录，查看speciality表是否新增/更新对应专业的统计数据
2. 删除这条students记录，查看speciality表的对应数据是否同步减少（或删除）
3. 修改学生的专业或当前学分，查看两个专业的统计数据是否正确调整

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