星期四, 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 不會

沒有留言:

張貼留言