SQL 觸發器：基本指南

已發表: 2022-10-12

觸發器是 SQL Server 中的數據庫實體。從技術上講，它們是響應特定數據庫操作的特定類函數調用。

本基本指南將為您提供有關 SQL 觸發器的深入信息，這些信息對您的職業非常有用。讓我們開始吧！

什麼是 SQL 觸發器？

“觸發器”一詞描述了每次更改數據庫中的內容時服務器自動執行查詢的語句。

觸發器是存儲在內存空間中的一組特殊命名的 SQL 查詢。它是一種特定類型的函數調用，每當發生任何數據庫事件時都會立即調用它。每個觸發器都有一個分配給它的表。

例如，只要將新列添加到特定表或更改特定記錄，就可以激活觸發器。

根據 Microsoft Developer Network，觸發器是一類特殊的存儲過程。在觸發器語句中，我們最初定義觸發器應該何時運行，然後我們提供觸發器激活後應該採取的操作。

句法：

 CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tableName FOR EACH ROW SET operation [trigger_body];

各參數說明

CREATE TRIGGER trigger_name – 用於構造觸發器或更改現有觸發器的名稱。
BEFORE/AFTER – 此查詢用於定義觸發器的執行時間（在某個事件之前或之後）。
INSERT/UPDATE/DELETE - 這描述了我們希望對錶執行的操作。
ON tableName – 在這裡，我們定義表名來設置觸發器。
FOR EACH ROW – 此語句與行觸發器相關，這意味著只要更改行，就會執行觸發器。
trigger_body – 它指定觸發器被激活時要採取的操作。

觸發器是具有獨特身份的存儲函數，使我們能夠重用已經執行並安全存儲在內存中的查詢。現在讓我們試著理解為什麼 SQL 需要它們。

觸發器主要用於在事件發生時規范代碼的部署。換句話說，如果您需要不斷運行特定的代碼片段以響應指定的事件，那麼使用觸發器是理想的選擇。

以下是在 SQL 數據庫操作中使用觸發器的一些好處。

在受影響的表中插入、更新或刪除數據時執行附加檢查。
縮短響應時間，這有助於提高計算費用。
啟用初始約束無法訪問的複雜默認參數的編碼。

參照完整性是關係數據庫系統的關鍵核心屬性。這意味著保存在數據庫系統中的數據對於每個事務和每個操作都必須始終準確。

如果兩個表位於不同的數據庫或系統上，則無法使用約束值確保其中的數據驗證。在這種情況下，觸發器是唯一的執行選項。

觸發參數的組合

對於每個表，我們可以指定六種不同的觸發器類型。這些是 SQL 行級觸發器中包含的觸發器參數的組合。

BEFORE INSERT ：這些觸發器在指定表或數據庫中執行任何 INSERT 操作之前對行執行操作。

AFTER INSERT ：它在任何數據庫 INSERT 活動之後立即對行執行操作。

BEFORE UPDATE ：使用這些觸發器，在對數據庫執行 UPDATE 操作之前對行執行一個函數。

AFTER UPDATE ：它在任何數據庫或特定表 UPDATE 活動之後立即對行執行操作。

BEFORE DELETE ：它甚至在數據庫或表受到 DELETE 操作之前對行執行特定操作。

AFTER DELETE ：這些觸發器在每個 DELETE 事務之後的行上執行操作。

SQL 觸發器的類型

SQL 觸發器是在特定事件發生時立即運行的存儲函數。它類似於事件驅動的調度。後續情況可以開始執行觸發器。

DML 觸發器——DML 代表數據操作語言。使用 DML 觸發器可以執行代碼以響應數據修改。當執行 INSERT、UPDATE 和 DELETE 等 DML 命令時，將激活此觸發器。這些也稱為“表級觸發器”。

DDL 觸發器——DDL 代表數據定義語言。 DDL 觸發器使我們能夠運行代碼以響應數據庫架構更改（例如添加或刪除表）或服務器事件（例如用戶簽入時）。這些稱為“數據庫級觸發器”。

當在活動數據庫中執行某些 DDL 語句（例如 CREATE、ALTER 或 DROP）時，可以激活這些觸發器。這些也可以用來監視和管理正在執行的活動。

登錄觸發器– 每當發生任何登錄（啟動、登錄、註銷、關閉）事件時，都會立即調用登錄觸發器。即使在用戶事務啟動之前，它們也僅在用戶身份驗證過程之後執行。如果授權失敗，則不會觸發 LOGON 觸發器。

這些觸發器可用於記錄登錄歷史或為特定登錄建立事件限制，以及服務器連接的其他審計和身份管理功能。

CLR 觸發器——CLR 代表公共語言運行時。 CLR 觸發器確實是主要基於 .NET 技術中的 CLR 構建的觸發器的獨特子集。如果觸發器需要執行大量計算或需要與 SQL 以外的實體相關聯，這些觸發器會很有幫助。

DML 和 DDL 觸發器確實可以通過啟用 .NET 技術（包括 Visual Basic、C# 和 F-sharp）中支持的 CLR 觸發器的編碼來構建。

示例 SQL Server 觸發器

讓我們通過一個例子來理解這些觸發器的概念。

首先，讓我們使用 SQL 語句創建一個數據庫。

 CREATE DATABASE testdb; use testdb;

在這裡，我給出了一個“testdb”作為數據庫的名稱。下一步是創建一個表。

 CREATE TABLE student( name varchar(25), id int(2), maths int(2), physics int(2), biology int(2), social int(2), total int(2) );

我創建了一個用於存儲學生詳細信息的表。這是描述表結構的命令。這裡的“student”是我給的表名。

 DESC student;

下面是我創建的表的結構。

 +---------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+-------------+------+-----+---------+-------+ | name | varchar(25) | YES | | NULL | | | id | int | YES | | NULL | | | maths | int | YES | | NULL | | | physics | int | YES | | NULL | | | biology | int | YES | | NULL | | | social | int | YES | | NULL | | | total | int | YES | | NULL | | +---------+-------------+------+-----+---------+-------+ 7 rows in set (0.00 sec)

創建表後，下一步是設置觸發器。讓我們嘗試使用BEFORE INSERT參數。

我創建的觸發器的名稱是“marks”。一旦使用學生的成績修改了表格，下面的觸發器就會嘗試自動確定學生的總成績。

 CREATE TRIGGER marks BEFORE INSERT ON student FOR EACH ROW set new.total=new.maths+new.physics+new.biology+new.social;

由於我們需要替換行數據而不是使用舊數據，因此我們使用新的類名定義了“total”，並且所有後續表達式都使用點運算符在 total 之後使用新關鍵字作為前綴。現在，我們將為每一行添加值並查看結果。最初，每個學生的總分都是 0。

 INSERT INTO student VALUES("George",02,99,87,92,91,0); INSERT INTO student VALUES("James",03,91,81,94,90,0); INSERT INTO student VALUES("Harry",04,86,70,73,88,0); INSERT INTO student VALUES("John",05,73,89,78,92,0); INSERT INTO student VALUES("Lisa",01,94,75,69,79,0);

在這種情況下，當向學生表中插入數據時，將自動觸發觸發器語句。每個學生的總分將由觸發器計算。現在，讓我們看看是否使用SELECT語句調用了觸發器。

 SELECT * FROM table_name;

這是最終的輸出。

 mysql> select * from student; +--------+------+-------+---------+---------+--------+-------+ | name | id | maths | physics | biology | social | total | +--------+------+-------+---------+---------+--------+-------+ | George | 2 | 91 | 81 | 94 | 90 | 356 | | James | 3 | 86 | 70 | 73 | 88 | 317 | | Harry | 4 | 73 | 89 | 78 | 92 | 332 | | John | 5 | 94 | 75 | 69 | 79 | 317 | | Lisa | 1 | 99 | 87 | 92 | 91 | 369 | +--------+------+-------+---------+---------+--------+-------+ 5 rows in set (0.00 sec)

在上面的結果中，您可以看到每個學生的所有科目分數都是自動添加的。所以，我們可以斷定觸發器被成功調用。

附加觸發操作

我們可以使用觸發器執行許多操作。有些可能很簡單，有些可能有點複雜，但是一旦我們通過查詢就很容易理解了。通過使用 Transact-SQL 語句，您可以使用以下命令啟用、禁用或刪除觸發器。

查詢以檢查特定的觸發器是否存在

此命令檢查整個數據庫中的指定觸發器。

 SELECT * FROM [sys].[triggers] WHERE [name] = 'Trigger_name'

查詢以顯示觸發器

以下語句將顯示活動數據庫中可用的所有觸發器。

 SHOW TRIGGERS;

查詢禁用觸發器

以下命令停用工作數據庫中的觸發器。

 DISABLE TRIGGER trigger_name ON DATABASE;

您還可以指定某個表名來禁用觸發器。

 DISABLE TRIGGER trigger_name ON table_name;

查詢以啟用觸發器

以下命令首先停用在活動數據庫中的指定表上定義的特定觸發器，然後再重新啟用它。

 ALTER TABLE table_name DISABLE TRIGGER trigger_name ALTER TABLE table_name ENABLE TRIGGER trigger_name

在嘗試啟用觸發器之前，必須禁用觸發器，

查詢以啟用或禁用表中的所有觸發器

使用上面的 SQL 語句，我們可以通過用“ALL”代替特定的觸發器名稱來一次停用或激活所有表觸發器。

 ALTER TABLE table_name DISABLE TRIGGER ALL ALTER TABLE table_name ENABLE TRIGGER ALL

查詢刪除或刪除觸發器

可以通過刪除觸發器或整個表來消除觸發器。當一個表被刪除時，每個相關的觸發器也會被刪除。

 DROP TRIGGER [trigger_name];

每當刪除觸發器時，相關數據都會從 sys.objects 數據表中刪除。

觸發器的優點

構建觸發器很簡單，觸發器本身可以調用存儲的函數和方法。
用戶可以使用觸發器實現簡單的審計。
可悲的是，您無法使用 SQL Server 在數據庫系統中創建跨實體的約束，儘管您可以通過使用觸發器來模擬約束的操作。
完整性約束可以使用觸發器跨數據庫實現。
當需要組驗證而不是對新輸入或更改的數據進行逐行驗證時，觸發器會很有幫助。

觸發器的缺點

由於其局限性，SQL 觸發器在某些情況下可能不是最佳選擇。

觸發器必須準確記錄。
由於應用程序組件可能無法訪問同時執行的數據庫，觸發器可能難以調試。
使用觸發器時，DML 語句變得更加複雜。
即使是輕微的觸發器問題也有可能導致語句中的邏輯錯誤。

結論

觸發器是 Transact-SQL 和 SQL 的非常有用的組件，您也可以在 Oracle 中使用它們。調用存儲方法時，觸發器的使用至關重要。這些 SQL 觸發器允許我們分析活動時間線並確定在必要時如何響應它們。我們還可以檢查連接到觸發器以獲取數據的某個表。

遞歸可以通過觸發器啟用。每當表上的觸發器在父表上執行命令時，觸發器的第二次迭代就會被觸發，這稱為遞歸觸發器。這在嘗試解決身份相關性時有所幫助。

此外，觸發器規定了允許數據庫接受的更新模式。如果 SQL 約束鍵主要不存在主鍵和外鍵，那麼在數據庫系統中保持數據完整性約束是非常有益的。

我希望這篇文章對您了解 SQL 觸發器有幫助。

如果你想深入學習數據庫，這裡有一些學習 SQL 和 NoSQL 的優秀資源。