[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

參考資料
• T-SQL: Error Handling within Triggers
• 減少網路傳輸量，進而提升效能，以設定 SET NOCOUNT ON 不要回傳所影響的資料列筆數之訊息為例
  

[VFP] CursorAdapter 和參數相依

VFP CursorAdapter SelectCommand 內的 TSQL 語法，可以直接抓參數塞入，參數和 CursorAdapter 有了相依，萬一參數名稱要進行修正，還要進到 SelectCommand 內去修正，那不幸該 CursorAdapter 用在多個地方，就變成一件大工程，同事修改 Code 時就遇上該情況

loDemoTable = Createobject("caCollection.caDemoTable")
liID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)

討論時，直覺是在 CursorAdapter 建立 Property，外面針對 Property 來進行設定，CursorAdapter SelectCommand 就直接抓該 Property 就行

loDemoTable = Createobject("caCollection.caDemoTable")
loDemoTable.ID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)

經過測試，確認 SelectCommand 是可以直接抓 Property 來當成參數，SQL Profile 內都是相同的 TSQL 語法

VFP 寫久麻痺，沒有考慮到兩者相依性問題

電源插頭轉換器

研討會問券禮，實用

[EF] 使用 Function

看見網路問題才發現到，原來 EF 可以使用 SQL Server 內的使用者自訂函數，利用 AdventureWorks2017 來筆記一下 ，兩個重點

• 呼叫 SQL Server 的使用者自訂函數
• 利用 EF 6.0 的 DbFunctions 來使用 SQL Server 日期函數

使用 AdventureWork2017 內現有的 Function - ufnGetSalesOrderStatusText

CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
GO

使用 [ADO.NET 實體資料模型] 把 [SalesOrderHeader] Table 和 [ufnGetSalesOrderStatusText] Function 拉進來使用

利用 partial class 擴充 DbContext 類別

using System.Data.Entity;

namespace EFSQLFunction
{
    public partial class EFDBContext
    {                  
        // SQL Server 名稱為 ufnGetSalesOrderStatusText，在 VS 內為滿足命名規則，把前綴詞的 ufn 移除
        [DbFunction("AdventureWorks2017Model.Store" , "ufnGetSalesOrderStatusText")]
        public string GetSalesOrderStatusText(byte Status)
        {
            throw new NotSupportedException("Direct calls are not Support");
        }
    }
}

DbFunctionAttrubute 參數一為 namespace、參數二為 Function 名稱，可以參考下圖輸入

驗證在 LINQ 內使用 Function 能轉成 TSQL 語法

using System.Data.Entity;

namespace EFSQLFunction
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EFDBContext context = new EFDBContext())
            {
                context.Database.Log = Console.Write;

                DateTime target = new DateTime(2011, 5, 31);
                context.SalesOrderHeader
                    // 透過 DbFunctions 來使用內建 Function
                    .Where(w => w.OrderDate >= target
                    && w.OrderDate <= DbFunctions.AddDays(target, 7))
                    .Select(s => new
                    {
                        s.SalesOrderNumber,
                        s.Status,
                        // 呼叫自定義的 Function
                        StatusText = context.GetSalesOrderStatusText(s.Status)
                    })
                    .ToList();
            }
        }
    }
}

觀察產生的 TSQL 語法

無法轉換的話，會拋出下圖的 Exception

參考資料
• DbFunctionAttribute(String, String) Constructor
• DbFunctions Class
• 如何：呼叫自訂資料庫函式
• 在 EF 中使用自定義的 SQL Function
• 在 LINQ to Entities 查詢中呼叫函式