在 SQL Server 中，觸發器是一種特殊類型的存儲過程，它會在滿足特定條件時自動執行。觸發器可以用于維護數據的完整性、執行業務規則、記錄數據變更等。
觸發器分為兩大類：DML 觸發器（包括 INSERT、UPDATE 和 DELETE）和 DDL 觸發器（例如 CREATE、ALTER 和 DROP）。
實例數據表：AuditLog
假設我們有一個 AuditLog 表，用于記錄 Employees 表中數據的變更情況。
| LogID | EmployeeID | Operation | ChangeDate |
|---|
| 1 | 2 | INSERT | 2023-01-01T12:00:00Z |
| 2 | 3 | UPDATE | 2023-01-02T15:30:00Z |
-- 創建AuditLog表CREATE TABLE AuditLog ( LogID INT IDENTITY(1,1) PRIMARY KEY, EmployeeID INT, Operation VARCHAR(100), ChangeDate DATETIME);
-- 插入AuditLog表數據INSERT INTO AuditLog (EmployeeID, Operation, ChangeDate)VALUES (2, 'INSERT', '2023-01-01T12:00:00Z'), (3, 'UPDATE', '2023-01-02T15:30:00Z');
實例數據表：Employees
我們還有一個 Employees 表，用于存儲員工信息。
| EmployeeID | FirstName | LastName | Position |
|---|
| 1 | Jane | Doe | Manager |
| 2 | John | Smith | Developer |
| 3 | Alice | Johnson | Developer |
-- 創建Employees表CREATE TABLE Employees ( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), Position VARCHAR(100),);
-- 插入Employees表數據INSERT INTO Employees (FirstName, LastName, Position)VALUES ('Jane', 'Doe', 'Manager'), ('John', 'Smith', 'Developer'), ('Alice', 'Johnson', 'Developer');
示例腳本 1：創建 INSERT 觸發器
創建一個觸發器，每當有新員工添加到 Employees 表時，就在 AuditLog 表中記錄這個操作。
CREATE TRIGGER tr_Employees_AuditLog_Insert
ON Employees
AFTER INSERT
AS
BEGIN
INSERT INTO AuditLog (EmployeeID, Operation, ChangeDate)
SELECT i.EmployeeID, 'INSERT', GETUTCDATE()
FROM inserted i;
END;
GO
在上面的觸發器中，inserted 是一個特殊的表，它在 INSERT 操作期間存儲了新插入的行。
示例腳本 2：執行 INSERT 操作
INSERT INTO Employees (FirstName, LastName, Position)
VALUES ('Emily', 'Clark', 'HR');
GO
執行這個 INSERT 操作后，觸發器 tr_Employees_AuditLog_Insert 會自動執行，并在 AuditLog 表中插入一條新記錄。
示例腳本 3：創建 UPDATE 觸發器
創建一個觸發器，每當 Employees 表的記錄被更新時，在 AuditLog 表中記錄這個操作。
CREATE TRIGGER tr_Employees_AuditLog_Update
ON Employees
AFTER UPDATE
AS
BEGIN
INSERT INTO AuditLog (EmployeeID, Operation, ChangeDate)
SELECT i.EmployeeID, 'UPDATE', GETUTCDATE()
FROM inserted i;
END;
GO
在這個觸發器中，inserted 表存儲了更新操作后的新值。
示例腳本 4：執行 UPDATE 操作
UPDATE Employees
SET Position = 'Senior Developer'
WHERE EmployeeID = 2;
GO
執行這個 UPDATE 操作后，觸發器 tr_Employees_AuditLog_Update 會自動執行，并在 AuditLog 表中插入一條新記錄。
示例腳本 5：創建 DELETE 觸發器
創建一個觸發器，每當從 Employees 表中刪除記錄時，在 AuditLog 表中記錄這個操作。
CREATE TRIGGER tr_Employees_AuditLog_Delete
ON Employees
AFTER DELETE
AS
BEGIN
INSERT INTO AuditLog (EmployeeID, Operation, ChangeDate)
SELECT d.EmployeeID, 'DELETE', GETUTCDATE()
FROM deleted d;
END;
GO
在這個觸發器中，deleted 表存儲了被刪除的行。
示例腳本 6：執行 DELETE 操作
DELETE FROM EmployeesWHERE EmployeeID = 3;GO
執行這個 DELETE 操作后，觸發器 tr_Employees_AuditLog_Delete 會自動執行，并在 AuditLog 表中插入一條新記錄。
觸發器的注意事項
觸發器可以帶來額外的開銷，因此在設計時要考慮性能影響。
觸發器可以嵌套和遞歸，需要謹慎使用以避免復雜的依賴和難以追蹤的錯誤。
在事務中使用觸發器時，觸發器的操作會成為事務的一部分，如果觸發器失敗，整個事務可能會回滾。
總結
觸發器是 SQL Server 中強大的工具，能夠自動化和強化數據完整性、審計和業務邏輯的實施。正確地使用觸發器可以幫助維護數據的一致性和可追溯性，但也要注意它們可能帶來的性能和復雜性問題。設計觸發器時，務必充分測試以確保它們的行為符合預期，并且不會對系統性能造成不利影響。
該文章在 2024/2/21 12:21:34 編輯過
400 186 1886
