| 維度 | DB2 | SQL Server |
|---|---|---|
| 觸發時機關鍵字 | BEFORE / AFTER / INSTEAD OF | AFTER / INSTEAD OF (無 BEFORE) |
| 新舊資料引用 | REFERENCING NEW AS N_ROW (可自訂別名) | inserted / deleted 虛擬表 (固定名稱) |
| 行級觸發 | FOR EACH ROW MODE DB2SQL 明確指定 | 隱含行級 (觸發器內處理多列) |
| 變數宣告 | DECLARE var TYPE; (無前綴) | DECLARE @var TYPE; (@ 前綴) |
| 賦值語法 | SET var = value; 或 SELECT ... INTO var FROM ... | SET @var = value; / SELECT @var = col FROM ... |
| 異常中止 | SIGNAL SQLSTATE '80000' ('msg') | THROW 50000, 'msg', 1; / RAISERROR |
| 觸發後預設行為 | 無錯誤 → 隱含提交 DML | 無錯誤 → 不隱含執行原 DML，需在觸發器內重寫 INSERT/UPDATE/DELETE |
CREATE TRIGGER EAS.trName
NO CASCADE BEFORE INSERT ON EAS.T_User
REFERENCING NEW AS N_ROW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE U_xtfidemp1 VARCHAR(36);
DECLARE u_xtempcode1 VARCHAR(20);
DECLARE u_xtempcodeCount INT;
DECLARE U_xtfidempCount INT;
DECLARE u_id1 INT;
SET U_xtfidemp1 = N_ROW.U_xtfidemp;
SET u_xtempcode1 = N_ROW.u_xtempcode;
SET u_id1 = N_ROW.u_id;
SET u_xtempcodeCount = (SELECT COUNT(u_xtempcode) FROM EAS.T_User
WHERE u_xtempcode IS NOT NULL AND u_xtempcode = u_xtempcode1 AND u_id <> u_id1);
SET U_xtfidempCount = (SELECT COUNT(U_xtfidemp) FROM EAS.T_User
WHERE U_xtfidemp IS NOT NULL AND U_xtfidemp = U_xtfidemp1 AND u_id <> u_id1);
IF u_xtempcodeCount > 0 OR U_xtfidempCount > 0 THEN
SIGNAL SQLSTATE '80000' ('EAS.T_User Exceeds u_xtempcode,U_xtfidemp 重複');
END IF;
END
CREATE TRIGGER EAS.trName
ON EAS.T_User
FOR INSERT -- SQL Server 無 BEFORE，用 AFTER (預設) 或 INSTEAD OF
AS
BEGIN
DECLARE @U_xtfidemp1 VARCHAR(36);
DECLARE @u_xtempcode1 VARCHAR(20);
DECLARE @u_xtempcodeCount INT;
DECLARE @U_xtfidempCount INT;
DECLARE @u_id1 INT;
-- 從 inserted 虛擬表取值 (支援多列)
SELECT @U_xtfidemp1 = U_xtfidemp, @u_xtempcode1 = u_xtempcode, @u_id1 = u_id FROM inserted;
SET @u_xtempcodeCount = (SELECT COUNT(u_xtempcode) FROM EAS.T_User
WHERE u_xtempcode IS NOT NULL AND u_xtempcode = @u_xtempcode1 AND u_id <> @u_id1);
SET @U_xtfidempCount = (SELECT COUNT(U_xtfidemp) FROM EAS.T_User
WHERE U_xtfidemp IS NOT NULL AND U_xtfidemp = @U_xtfidemp1 AND u_id <> @u_id1);
IF @u_xtempcodeCount > 0 OR @U_xtfidempCount > 0 BEGIN
-- INSTEAD OF 觸發器中需自行決定是否執行原 INSERT
-- AFTER 觸發器中拋錯誤會回滾原 INSERT
THROW 50000, 'EAS.T_User Exceeds u_xtempcode,U_xtfidemp 重複', 1;
END
END
BEFORE 觸發器在 DML 執行前執行，可修改 N_ROW 內容影響寫入值。BEFORE。AFTER 在 DML 完成後執行 (同交易內)；INSTEAD OF 取代原 DML，觸發器必須自行執行 INSERT/UPDATE/DELETE，否略原操作不會發生。FOR EACH ROW 明確逐列觸發，N_ROW 為單列記錄。inserted/deleted 含所有影響列。需用 JOIN inserted 或游標逐列處理，或接受集合式邏輯 (如上例 SELECT ... FROM inserted 只取第一列，實務應處理多列)。SIGNAL 拋出自定義 SQLSTATE，觸發器失敗導致原 DML 回滾。THROW / RAISERROR 拋錯，AFTER 觸發器中會回滾原 DML；INSTEAD OF 中拋錯僅回滾觸發器內交易，原 DML 已被取代不存在。BEFORE INSERT/UPDATE → INSTEAD OF INSERT/UPDATE (需重寫 DML) 或 AFTER (若僅驗證)。REFERENCING NEW AS X → inserted / deleted。@ 前綴。SET var = val → SET @var = val；SELECT ... INTO var → SELECT @var = col FROM ...。SIGNAL → THROW (SQL 2012+) 或 RAISERROR。inserted 處理邏輯 (集合式 vs 逐列)。INSTEAD OF 觸發器中是否正確重寫原 DML。DB2 與 SQL Server 觸發器在觸發時機、行級/陳述式級、虛擬表存取、異常語法、預設 DML 行為五大維度存在本質差異。遷移時不可逐行翻譯，必須理解語意並重寫邏輯。建議將複雜業務規則移至預存程序/應用層，觸發器僅作最後防線 (稽核、同步、不可由應用層保證的約束)。
