動態

詳情 返回 返回

什麼是觸發器？ - 動態 詳情

在 SQL Server 數據庫管理中，觸發器能夠在特定的數據庫操作發生時自動執行預先定義的 SQL 語句。觸發器可以用於實現數據完整性約束、審計跟蹤、複雜的業務邏輯等多種功能。本文將深入探討 SQL Server 觸發器(trigger)的概念、類型、工作原理以及實際應用，並通過代碼示例進行演示。

觸發器的概念

觸發器(trigger)是SQL server提供給程序員和數據分析員用來保證數據完整性的一種方法,它是與表事件相關的特殊的存儲過程,它的執行不是由程序調用,也不是手工啓動,而是由事件來觸發。當對一個表進行插入（INSERT）、更新（UPDATE）或刪除（DELETE）操作時就會激活它執行。觸發器經常用於加強數據的完整性約束和業務規則等。

觸發器與存儲過程的區別是運行方式的不同,觸發器不能執行EXECUTE語句調用,而是在用户執行 Transact-SQL 語句時自動觸發執行，而存儲過程需要用户,應用程序或者觸發器顯示地調用並執行，這是兩者最明顯的區別。

例如，當一個新的訂單被插入到訂單表中時，可以使用觸發器自動更新庫存表中的庫存數量，以確保庫存不會出現負數。或者，當一個員工的工資被更新時，可以使用觸發器記錄工資變更的歷史記錄，以便進行審計。

觸發器的作用

觸發器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的複雜參照完整性和數據的一致性,它能夠對數據庫中的相關表進行級聯修改,提高比CHECK約束更復雜的的數據完整性,並自定義錯誤消息。觸發器的主要作用主要有以下幾個方面:

  1. 強制數據庫間的引用完整性
  2. 級聯修改數據庫中所有相關的表,自動觸發其它與之相關的操作
  3. 跟蹤變化,撤銷或回滾違法操作,防止非法修改數據
  4. 返回自定義的錯誤消息,約束無法返回信息,而觸發器可以
  5. 觸發器可以調用更多的存儲過程

觸發器的類型

Sql Server 通常包括三種常規類型的觸發器: DML觸發器、DDL觸發器和登錄觸發器。

DML 觸發器

DML(數據操作語言,Data Manipulation Language)觸發器是一些附加在特定表或視圖上的操作代碼,當數據庫服務器中發生數據操作語言事件（如 INSERT、UPDATE 和 DELETE）時執行這些操作。

當遇到下列情形時,應考慮使用DML觸發器:

  1. 通過數據庫中的相關表實現級聯更改
  2. 防止惡意或者錯誤的insert、update和delete操作,並強制執行check約束定義的限制更為複雜的其他限制。
  3. 評估數據修改前後表的狀態,並根據該差異才去措施。

根據觸發的時機，DML 觸發器又可以分為以下兩種：

  1. AFTER 觸發器：在觸發 DML 操作（INSERT、UPDATE 或 DELETE）完成後觸發。AFTER 觸發器可以用於檢查數據的完整性、執行額外的業務邏輯或記錄數據變更的歷史記錄。
  2. INSTEAD OF 觸發器：在觸發 DML 操作被執行之前觸發，並替代原始的 DML 操作。INSTEAD OF 觸發器可以用於實現複雜的業務邏輯，例如在特定條件下阻止數據的插入、更新或刪除，或者將數據插入到其他表中。

DDL 觸發器

DDL(數據定義語言,Data Definition Language)觸發器是在服務器或者數據庫中數據定義語言(主要是以

CREATE,DROP,ALTER開頭的語句)作用於數據庫對象（如表、視圖、存儲過程等）時觸發的,使用DDL觸發器可以防止意外的數據庫結構變更或記錄數據中的更改或事件操作。

登錄觸發器

登錄觸發器將為響應 LOGIN 事件而激發存儲過程。SQL Server身份驗證階段完成之後且用户會話實際建立之前激發。因此,來自觸發器內部且通常將到達用户的所有消息(例如錯誤消息和來自 PRINT 語句的消息)會傳送到 SQL Server 錯誤日誌。如果身份驗證失敗,將不激發登錄觸發器。

按照特殊的執行方式又可以分為：嵌套觸發器、遞歸觸發器。

嵌套觸發器

概念：

嵌套觸發器是指一個觸發器在執行過程中觸發了另一個觸發器的情況。SQL Server 允許觸發器進行嵌套調用，但可以通過數據庫選項來控制嵌套的層數。

工作原理：

當一個數據操作（如 INSERT、UPDATE 或 DELETE）觸發了一個表上的觸發器時，如果這個觸發器的執行又導致了另一個表上的數據操作，並且該表上也有觸發器，那麼就可能會發生嵌套觸發。

例如，有兩個表 TableA 和 TableB，在 TableA 上有一個觸發器 TriggerA，當對 TableA 進行數據操作時觸發了 TriggerA。而 TriggerA 的執行過程中對 TableB 進行了數據操作，TableB 上有觸發器 TriggerB，此時就發生了嵌套觸發。

配置與限制：

在 SQL Server 中，可以通過數據庫選項“nested triggers”來控制是否允許嵌套觸發器以及嵌套的層數。默認情況下，該選項是開啓的，允許嵌套觸發器，但可以設置為關閉以禁止嵌套觸發或限制嵌套的層數。

如果嵌套層數過多，可能會導致性能問題和複雜的調試情況。

遞歸觸發器

概念

遞歸觸發器是一種特殊的觸發器，它允許一個表上的觸發器在執行過程中再次觸發自身。這可以用於處理一些具有遞歸性質的數據操作。

工作原理

當對一個表進行數據操作時，觸發了該表上的觸發器。如果這個觸發器的執行又導致了對同一個表的相同類型的數據操作，並且數據庫選項“recursive triggers”是開啓的，那麼就會再次觸發這個觸發器，形成遞歸調用。

例如，有一個表 TableC，上面有一個觸發器 TriggerC。當對 TableC 進行數據操作時觸發了 TriggerC，而 TriggerC 的執行過程中又對 TableC 進行了數據操作，此時如果遞歸觸發器選項開啓，TriggerC 會再次被觸發。

配置與應用場景：

可以通過數據庫選項“recursive triggers”來控制是否允許遞歸觸發器。默認情況下，該選項是關閉的。

遞歸觸發器的應用場景相對較少，通常用於處理一些具有層次結構或遞歸關係的數據，例如組織架構表中上下級關係的更新等。但在使用遞歸觸發器時需要特別小心，因為如果遞歸沒有正確控制，可能會導致無限循環，從而消耗大量的系統資源甚至使數據庫陷入死鎖。

在使用嵌套觸發器和遞歸觸發器時需要謹慎考慮其性能影響和潛在的複雜性，確保數據庫的穩定和高效運行。

觸發器的工作原理

觸發器觸發時

  1. 系統自動在內存中創建deleted表或inserted表;
  2. 只讀,不允許修改,觸發器執行完成後,自動刪除。

inserted表

  1. 臨時保存了插入或更新後的記錄行;
  2. 可以從inserted表中檢查插入的數據是否滿足業務需求;
  3. 如果不滿足,則向用户發送報告錯誤消息,並回滾插入操作。

deleted表

  1. 臨時保存了刪除或更新前的記錄行;
  2. 可以從deleted表中檢查被刪除的數據是否滿足業務需求;
  3. 如果不滿足,則向用户報告錯誤消息,並回滾插入操作。

Update操作時

新的記錄存入inserted表,舊的記錄存入deleted表。

觸發器的創建和使用

創建 DML 觸發器

以下是創建一個 AFTER INSERT DML 觸發器的示例代碼，用於在訂單表中插入新訂單後自動更新庫存表中的庫存數量：

img

商品1的原始庫存為751

img

向訂單表中插入一條訂單，下單10個商品1

img

執行完插入語句後，查看庫存表可以發現商品1的數量自動減10

img

在這個示例中，我們創建了一個名為UpdateInventory的觸發器，它在Orders表上的 INSERT 操作完成後觸發。觸發器中的 SQL 語句用於更新庫存表中的庫存數量，將庫存數量減去新插入訂單中的數量。

創建 DDL 觸發器

以下是創建一個 DDL 觸發器的示例代碼，用於記錄數據庫結構變更的歷史記錄：

img

在這個示例中，我們創建了一個名為AuditDDLChanges的觸發器，它在數據庫級別上的CREATE_TABLE、ALTER_TABLE和DROP_TABLE操作發生時觸發。觸發器中的 SQL 語句用於將數據庫結構變更的事件類型、對象名稱和事件發生的日期插入到一個名為DDLChangeLog的表中，以便進行審計跟蹤。

使用 INSTEAD OF 觸發器

以下是創建一個 INSTEAD OF INSERT 觸發器的示例代碼。請注意：MySQL 中沒有直接等同於 SQL Server 的INSTEAD OF INSERT觸發器，我這裏使用BEFORE INSERT觸發器來實現類似的邏輯控制。用於在特定條件下阻止數據的插入：

img

img

在這個示例中，我們創建了一個名為PreventInsert的觸發器，它在orders表上的 INSERT 操作被執行之前觸發。觸發器中的 SQL 語句用於檢查插入的訂單中的數量是否小於等於零，如果是，則拋出一個錯誤消息，阻止數據的插入；如果不是，則將插入的數據插入到訂單表中。

觸發器的優點和缺點

優點

  1. 數據完整性：觸發器可以在數據庫層面實現數據完整性約束，確保數據的一致性和準確性。例如，可以使用觸發器檢查插入或更新的數據是否符合特定的業務規則，如必填字段、數據範圍等。
  2. 自動化業務邏輯：觸發器可以自動執行復雜的業務邏輯，減少手動編碼的工作量。例如，可以使用觸發器在數據插入、更新或刪除時自動更新相關表中的數據，或者發送通知郵件等。
  3. 審計跟蹤：觸發器可以用於記錄數據變更的歷史記錄，以便進行審計跟蹤。例如，可以使用觸發器將數據變更的操作類型、操作時間、操作人員等信息記錄到一個審計表中。

缺點

  1. 性能影響：觸發器的執行會增加數據庫的負擔，特別是在頻繁發生數據修改操作的情況下，可能會影響數據庫的性能。因此，在設計觸發器時，需要考慮其對性能的影響，並儘量減少不必要的觸發器。
  2. 複雜性增加：觸發器的使用會增加數據庫的複雜性，特別是在多個觸發器相互作用的情況下，可能會導致難以調試的問題。因此，在設計觸發器時，需要考慮其對數據庫架構的影響，並儘量保持數據庫的簡潔性。
  3. 可維護性降低：觸發器的修改和維護可能會比較困難，特別是在觸發器的邏輯比較複雜的情況下。因此，在設計觸發器時，需要考慮其可維護性，並儘量使用簡單、清晰的邏輯。

總結

觸發器是可以在特定的數據庫事件發生時自動執行預先定義的 SQL 語句。通常用於實現數據完整性約束、審計跟蹤、複雜的業務邏輯等多種功能。然而，觸發器的使用也需要謹慎，因為它們可能會增加數據庫的複雜性和性能負擔。在設計和使用觸發器時，需要考慮其對數據庫架構、性能和可維護性的影響。

Chat2DB 文檔：https://docs.chat2db.ai/zh-CN/docs/start-guide/getting-started

Chat2DB 官網：https://chat2db.ai/zh-CN

Chat2DB GitHub：https://github.com/codePhiliaX/Chat2DB

SQL , 人工智能 , sqlserver , 數據庫 , MySQL
點贊

Add a new 評論

Some HTML is okay.