CLR只触发特定列得到更新 [英] CLR Trigger only particular column get updated

问题描述

我写了一个新文件时被插入到我的表，然后将值传递给我WCF服务CLR触发，现在我必须要改变的过程中，以更新只有特定的列中获取然后更新我也要拉从其他两个表中的值。

只是想知道这是反正我可以启动CLR触发仅仅只有特定列得到更新？

这样的场景

表1：客户详细信息（Cust.No，Cust.Name，DESC）结果
表2：地址（DoorNo，街道，城市，州）

下面是什么我试图做的，如果表1中的说明一栏得到更新则CLR触发得到。触发并通过基于说明中的表1和表2中的所有值

下面是我的插入代码：

  [Microsoft.SqlServer.Server.SqlTrigger（NAME =WCFTrigger，目标=tbCR事件=FOR UPDATE，INSERT）] 
公共静态无效触发器1（）
 {
的SqlCommand CMD; 
 SqlTriggerContext myContext = SqlContext.TriggerContext; 
 SqlPipe管= SqlContext.Pipe; 
 SqlDataReader的阅读器; 
 
如果（myContext.TriggerAction == TriggerAction.Insert）
 {使用
（康涅狄格州的SqlConnection =新的SqlConnection（@上下文连接=真正的））
 { 
 conn.Open（）; 
 // CMD =新的SqlCommand（@SELECT * FROM tbCR，康恩）; 
 CMD =新的SqlCommand（@SELECT * FROM插入的，康恩）; 
读者= cmd.ExecuteReader（）; 
 reader.Read（）; 
 //获取插入值，在这里
串Cust.No，Cust.Name，说明; 
 Cust.No =读者[0]的ToString（）; 
 Cust.Name =读者[1]的ToString（）; 
图示=读者[2]的ToString（）; 
 myclient.InsertOccured（Cust.No，Cust.Name，DESC）; 
 reader.Dispose（）; 
} 
} 
} 
  

解决方案

您不能防止选择性运行触发器，它总是会运行，无论更新的列。然而，一旦推出可以咨询 COLUMNS_UPDATED（） 功能：

返回指示表
或列的VARBINARY位模式视图插入或更新。 COLUMNS_UPDATED使用
随时随地的Transact-SQL INSERT或UPDATE触发器
测试触发器是否应该执行某些操作的体内。

所以，你会调整自己的触发逻辑有根据哪些列，其中更新appropiate行动。

话虽这么说，从SQLCLR调用WCF是一个非常非常糟糕的主意。从触发器调用WCF更是雪上加霜。您的服务器将在生产中死于事务会阻塞/中止等待一些HTTP响应抓取跨线回来。更何况，你的电话是在回滚的存在本身不正确，因为你不能撤消HTTP调用。做这样的动作正确的方法是由队列的手段去耦操作和WCF调用。作为队列你可以使用表做到这一点，你可以使用真正队列或你可以使用的更改跟踪。任何这些将使你解耦变化和WCF调用并允许你从一个单独的进程进行调用，而不是从SQLCLR

I wrote a clr trigger whenever a new file get inserted in to my table and then pass the value to my WCF service, now i have to change the process to "update" only the particular column get updated then i have to pull the value from other two tables.

Am just wondering is this anyway i can start the clr trigger just only the particular column get updated ?

The scenario like this

Table 1: Customer Details (Cust.No, Cust.Name,Desc)
Table 2: Address (DoorNo,Street,City,State).

Here what am trying to do, if the "Desc" column in Table1 get updated then the clr trigger get triggered and pass all the values in Table1 and Table2 based on the "Desc".

Here is my code for Insert:

[Microsoft.SqlServer.Server.SqlTrigger(Name = "WCFTrigger",Target = "tbCR", Event = "FOR UPDATE, INSERT")]
public static void Trigger1()
{
    SqlCommand cmd;
    SqlTriggerContext myContext = SqlContext.TriggerContext;
    SqlPipe pipe = SqlContext.Pipe;
    SqlDataReader reader;

    if(myContext.TriggerAction== TriggerAction.Insert)
    {
        using (SqlConnection conn = new SqlConnection(@"context connection=true"))
        {
            conn.Open();
            //cmd = new SqlCommand(@"SELECT * FROM tbCR", conn);
            cmd = new SqlCommand(@"SELECT * FROM INSERTED", conn);
            reader = cmd.ExecuteReader();
            reader.Read();
            //get the insert value's here
            string Cust.No, Cust.Name,Desc;
            Cust.No = reader[0].ToString();
            Cust.Name = reader[1].ToString();
            Desc = reader[2].ToString();
            myclient.InsertOccured(Cust.No, Cust.Name,Desc);
            reader.Dispose();
        }
    }
}

解决方案

You cannot prevent running the trigger selectively, it will always run no matter the columns updated. However, once launched you can consult the COLUMNS_UPDATED() function:

Returns a varbinary bit pattern that indicates the columns in a table or view that were inserted or updated. COLUMNS_UPDATED is used anywhere inside the body of a Transact-SQL INSERT or UPDATE trigger to test whether the trigger should execute certain actions.

So you would adjust your trigger logic to have appropiate action according to what columns where updated.

That being said, calling WCF from SQLCLR is a very very bad idea. Calling WCF from a trigger is even worse. Your server will die in production as transactions will block/abort waiting on some HTTP response to crawl back across the wire. Not to mention that your calls are inherently incorrect in presence of rollbacks, as you cannot undo an HTTP call. The proper way to do such actions is to decouple the operation and the WCF call by means of a queue. You can do this with tables used as queues, you could use true queues or you could use Change Tracking. Any of these would allow you to decouple the change and the WCF call and would allow you to make the call from a separate process, not from SQLCLR

这篇关于CLR只触发特定列得到更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！