~楓花雪岳~: [SQL] Trigger 應用

星期四, 1月 30, 2020

[SQL] Trigger 應用 - 跨 DB 條件約束

實務上有跨 DB 建立條件約束需求，因為 Foregin Key 限制 Table 彼此必須在相同 DB 內，轉向利用 Trigger 來達到該需求，剛好官方文件上也有類似範例

官方文件內容 - 建立外部索引鍵關聯性

FOREIGN KEY 條件約束只能參考在相同伺服器之相同資料庫內的資料表。跨資料庫參考完整性必須利用觸發程序來實作

官方文件內容 - CREATE TRIGGER - C. 使用 DML AFTER 觸發程序在 PurchaseOrderHeader 與 Vendor 資料表之間執行商務規則

CREATE TRIGGER Purchasing.LowCredit 
ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  

    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    IF EXISTS 
        (
            SELECT *  
            FROM Purchasing.PurchaseOrderHeader AS p   
                JOIN inserted AS i ON p.PurchaseOrderID = i.PurchaseOrderID   
                JOIN Purchasing.Vendor AS v ON v.BusinessEntityID = p.VendorID  
            WHERE v.CreditRating = 5  
        )  
        BEGIN  
            RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);  
            ROLLBACK TRANSACTION;  
            RETURN   
        END;  
GO

自行整理的 Trigger 語法

CREATE TRIGGER [dbo].[Trigger4FK]
    ON [dbo].[TableName]
    AFTER INSERT
AS 
    IF (ROWCOUNT_BIG() = 0)
        RETURN;

    IF EXISTS
        (
            SELECT L.KeyColumn
            FROM inserted AS L
            WHERE L.KeyColumn IS NOT NULL
                AND NOT EXISTS
                    (
                        SELECT 1
                        FROM CrossDB.dbo.CrossDBTable AS C
                        WHERE L.KeyColumn = C.KeyColumn
                    )
        )
        BEGIN
            -- SQL Server 2012 之前使用 RAISERROR
            RAISERROR ('TableName.KeyColumn 違反 FK' , 16 , 1)
            ROLLBACK TRANSACTION;  
            RETURN

            -- SQL Server 2012 開始使用 THROW
            THROW 50000 , 'TableName.KeyColumn 違反 FK' , 1 ;
        END  
GO

故意引發的錯誤訊息，訊息如下

訊息 50000，層級 16，狀態 1，程序 Trigger4FK，行 27 [批次開始行 44]
TableName.KeyColumn 違反 FK
訊息 3609，層級 16，狀態 1，行 45
交易在觸發程序中結束。已中止批次。

閱讀 Trigger 文件時，注意到這段說明：最佳化 DML 觸發程序

觸發程序會在交易中運作 (隱含或其他方式)，並在開啟時鎖定資源。這項鎖定會持續，直到確認 (使用 COMMIT) 或拒絕 (使用 ROLLBACK) 交易為止。觸發程序執行時間越長，封鎖其他處理序的機率越高。因此，撰寫觸發程序時，請盡可能降低其持續時間。要確保較短持續時間的方法之一，是在 DML 陳述式變更零個資料列時釋放觸發程序。若要針對不會變更任何資料列的命令釋放觸發程序，請使用系統變數 ROWCOUNT_BIG。

下列 T-SQL 程式碼片段示範如何針對不會變更任何資料列的命令釋放觸發程序。此程式碼應該出現在每個 DML 觸發程序的開頭：

IF (ROWCOUNT_BIG() = 0)
RETURN;

RAISERROR 和 THROW 相關重點

THROW 預設的嚴重性層級為 16
THROW 會回復整個批次，但是 RAISERROR 不會

[SQL] Trigger - inserted & deleted Table

~楓花雪岳~

星期四, 1月 30, 2020

[SQL] Trigger 應用 - 跨 DB 條件約束

沒有留言:

張貼留言