SQL Server 觸發器與交易實戰範例：新增、刪除、更新、預存程序、游標、視圖、函數

1. 新增/刪除觸發器

ALTER TRIGGER Tri_TC ON T_C
FOR INSERT, DELETE
AS
BEGIN
    SET XACT_ABORT ON;
    DECLARE @InsCnt INT = (SELECT COUNT(*) FROM Inserted);
    DECLARE @DelCnt INT = (SELECT COUNT(*) FROM Deleted);

    IF @InsCnt > 0
        INSERT INTO T_C2 SELECT * FROM Inserted;
    ELSE IF @DelCnt > 0
        DELETE T_C2 WHERE EXISTS (SELECT 1 FROM Deleted D WHERE D.CId = T_C2.CId);
END;

2. 更新觸發器 + 交易控制

ALTER TRIGGER Tri_TC_Update ON T_C
FOR UPDATE
AS
BEGIN
    DECLARE @DelCnt INT = (SELECT COUNT(*) FROM Deleted);
    IF @DelCnt > 0
    BEGIN
        BEGIN TRANSACTION TriUpdate;
        DECLARE @CName VARCHAR(100) = (SELECT CName FROM Inserted);
        
        UPDATE T_C2 SET CName = @CName WHERE CId = (SELECT CId FROM Deleted);
        
        IF @@ERROR <> 0
            ROLLBACK TRANSACTION TriUpdate;
        ELSE
            COMMIT TRANSACTION TriUpdate;
    END
END;

3. 預存程序建立與執行

IF EXISTS (SELECT 1 FROM SysObjects WHERE Name = 'Pro_Fun' AND Type = 'P')
    DROP PROCEDURE Pro_Fun;
GO

CREATE PROCEDURE Pro_Fun
AS
    SELECT * FROM TableName;
GO

EXEC Pro_Fun;

4. 游標逐列處理

DECLARE @QYBH VARCHAR(10);
DECLARE Cur CURSOR FOR SELECT DISTINCT QYBH FROM PJ_EnterpriseInput;
OPEN Cur;
FETCH NEXT FROM Cur INTO @QYBH;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT(@QYBH);
    FETCH NEXT FROM Cur INTO @QYBH;
END
CLOSE Cur;
DEALLOCATE Cur;

5. 視圖建立

ALTER VIEW CreateView AS SELECT QYBH FROM CreateView; -- 範例結構

6. 純量函數

ALTER FUNCTION FunName(@Str1 VARCHAR(10), @Str2 VARCHAR(10))
RETURNS VARCHAR(10)
AS
BEGIN
    DECLARE @Ret VARCHAR(10) = 'False';
    IF @Str1 > @Str2 SET @Ret = 'True';
    RETURN @Ret;
END;

SELECT dbo.FunName('A', 'B');

7. 表變數

DECLARE @QYBHTable TABLE (Id VARCHAR(32), QYBH VARCHAR(30));
INSERT INTO @QYBHTable SELECT Id, QYBH FROM PJ_EnterpriseInput;
SELECT * FROM @QYBHTable;

8. CASE WHEN 條件統計

SELECT 
    SUM(CASE WHEN Z.WatchName = N'註冊監理工程師' THEN 1 ELSE 0 END),
    SUM(CASE WHEN Z.WatchName = N'Xinza' THEN 1 ELSE 0 END),
    SUM(CASE WHEN Z.WatchName = N'監理員' THEN 1 ELSE 0 END)
FROM Zu_CorJl Z
RIGHT JOIN Zu_CorJlTemp T ON T.CorID = Z.CorID;

關鍵提醒

• 觸發器中 SET XACT_ABORT ON 確保錯誤時自動回滾
• 交易命名（BEGIN TRAN Name）便於嵌套管理
• 游標用畢必須 CLOSE + DEALLOCATE 釋放資源
• 表變數適用於小量暫存資料，大量資料用暫存表
• CASE WHEN 聚合統計避免多次掃描表

總結

本文彙整 SQL Server 進階程式設計常用模式：觸發器（DML 三類）、交易控制、預存程序、游標、視圖、函數、表變數、條件聚合，可作為開發參考範本。