SQL 選課觸發器實作：限制 10 人名額與即時顯示剩餘名額

業務需求

• 選課資訊表 elective 記錄學生選課。
• 每門課限額 10 人。
• 插入選課記錄前檢查目前已選人數：<=10 允許、>10 拒絕並提示。
• 需能查詢「已選人數」或「剩餘名額」。

資料結構調整建議

原設計將 Stu_No 設為 PK，導致一學生只能選一門課，不符合實況。建議：

1. 建立課程基本資料表 course_info：
   Cou_No (PK), Cou_Name, Max_Count (默認 10), Current_Count (默認 0)
2. 選課記錄表 elective：
   ID (PK), Stu_No (FK), Cou_No (FK), Ele_Date, Status

觸發器實作

CREATE TRIGGER trg_elective_insert
ON elective
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @Cou_No BIGINT, @Stu_No BIGINT, @Curr INT, @Max INT;

    -- 取 inserted 中的課程與學生（假設單筆插入，批次需游標）
    SELECT @Cou_No = Cou_No, @Stu_No = Stu_No FROM inserted;

    -- 檢查是否已選過
    IF EXISTS (SELECT 1 FROM elective WHERE Stu_No = @Stu_No AND Cou_No = @Cou_No)
    BEGIN
        RAISERROR('該學生已選修此課程', 16, 1);
        ROLLBACK;
        RETURN;
    END

    -- 讀取課程容量與目前人數
    SELECT @Max = Max_Count, @Curr = Current_Count
    FROM course_info WHERE Cou_No = @Cou_No;

    IF @Curr >= @Max
    BEGIN
        RAISERROR('課程名額已滿（已選 %d / %d 人），無法加選', 16, 1, @Curr, @Max);
        ROLLBACK;
        RETURN;
    END

    -- 允許插入
    INSERT INTO elective (Stu_No, Cou_No, Ele_Date, Status)
    SELECT Stu_No, Cou_No, GETDATE(), 'Normal' FROM inserted;

    -- 更新課程已選人數
    UPDATE course_info SET Current_Count = Current_Count + 1 WHERE Cou_No = @Cou_No;
END

查詢剩餘名額

SELECT Cou_Name, Max_Count, Current_Count, (Max_Count - Current_Count) AS Remaining
FROM course_info;

關鍵點總結

• 使用 INSTEAD OF INSERT 觸發器完全攔截插入行為，在邏輯層做業務判斷。
• 將「容量/已選數」冗余存於 course_info，避免每次 `COUNT(*)` 掃描大表，效能更佳。
• 若支援批次插入，觸發器內需用游標或 `JOIN inserted` 集合處理多筆。
• 刪除/退選時需建立對應 INSTEAD OF DELETE 觸發器，同步遞減 Current_Count。