廢廢小筆記

純筆記  純筆記  純筆記 不是教學文XD

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Net;
using System.Text;
using System.IO;

public partial class Triggers
{        
    // 輸入目標的現有資料表或檢視，並將屬性行取消註解
     [Microsoft.SqlServer.Server.SqlTrigger (Name="CallAPI", Target="test1", Event="FOR UPDATE")]
    public static void CallAPI ()
    {// 以您自己的程式碼取代
        SqlContext.Pipe.Send("K2 API");

        // 克難組一下json...
        StringBuilder json = new StringBuilder();
        string now = DateTime.Now.ToString("yyyyMMddHHmmss");
        json.AppendLine("{\"test\":\"" + now + "\"}");

        // 印出來看一下
        SqlContext.Pipe.Send(json.ToString());


        // 呼叫
        var webAddr = "http://localhost:58162/api/test";
        var httpWebRequest = (HttpWebRequest)WebRequest.Create(webAddr);
        httpWebRequest.Method = "POST";
        httpWebRequest.ContentType = "application/json";
        httpWebRequest.ContentLength = json.Length;

        using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
        {
            streamWriter.Write(json);
        }

        var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
        using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
        {
            var result = streamReader.ReadToEnd();
            SqlContext.Pipe.Send(result);
        }
    }
}

update 的結果

若只限定某個欄位被更新才觸發trigger的話可以用以下方法：

原則上只要table被更新trigger就會被觸發，但觸發時再判斷是否為該欄位

        string _AuditSQL = @"SELECT ins.* FROM   INSERTED ins;";
        SqlCommand _Command = _Connection.CreateCommand();
        _Command.CommandText = _AuditSQL;
        _Connection.Open();
        _Command.ExecuteNonQuery();
        SqlDataReader dr = _Command.ExecuteReader(CommandBehavior.SchemaOnly);

        if (SqlContext.TriggerContext.IsUpdatedColumn(dr.GetOrdinal("column_name")))
        {
            //欄位column_name被update時            
        }
        else
        {
            //非欄位column_name被update時
        }

column_name為被update的tabel的欄位名稱