使用了 Oracle/標準 SQL 風格：REFERENCING OLDROW AS oldtuple, NEWROW AS newtuple、FOR EACH ROW、BEGIN ... END IF;，**SQL Server 不支援**。
CREATE TRIGGER 月薪資變動
ON 考勤
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @oldDeduction MONEY, @newDeduction MONEY, @empId VARCHAR(20);
-- inserted / deleted 虛擬表僅有一列（單列更新）或多列
SELECT @oldDeduction = 一月份缺勤扣薪, @empId = 員工編號 FROM deleted;
SELECT @newDeduction = 一月份缺勤扣薪 FROM inserted;
IF (@oldDeduction != @newDeduction)
BEGIN
UPDATE 員工月薪資
SET 一月份薪資 = 一月份薪資 - @newDeduction + @oldDeduction
WHERE 員工編號 = @empId;
END
END;| 項目 | Oracle / 標準 SQL | SQL Server (T-SQL) |
|---|---|---|
| 虛擬表 | :OLD, :NEW / OLDROW, NEWROW | deleted, inserted |
| 行級觸發 | FOR EACH ROW | 無行級關鍵字，**預設即為語句級**；需自行在觸發器中處理多列（游標或集合操作） |
| 條件判斷 | IF ... THEN ... END IF; | IF ... BEGIN ... END |
| 變數宣告 | 隱式 | DECLARE @var TYPE |
若批次更新多筆，需用 JOIN inserted/deleted 集合寫法：
UPDATE p
SET 一月份薪資 = p.一月份薪資 - i.一月份缺勤扣薪 + d.一月份缺勤扣薪
FROM 員工月薪資 p
JOIN inserted i ON p.員工編號 = i.員工編號
JOIN deleted d ON p.員工編號 = d.員工編號
WHERE i.一月份缺勤扣薪 != d.一月份缺勤扣薪;
