CLR 触发仅更新特定列
每当新文件插入到我的表中时，我都会编写一个 clr 触发器，然后将值传递给我的 WCF 服务，现在我必须将流程更改为“更新”，仅更新特定列，然后我必须从中提取值另外两张桌子。
我只是想知道我是否可以启动 clr 触发器，只更新特定的列？
像这样的场景
表 1：客户详细信息（客户编号、客户名称、描述）
表 2：地址（门号、街道、城市、州）。
这里我想要做什么，如果 Table1 中的“Desc”列更新，则 clr 触发器被触发并根据“Desc”传递 Table1 和 Table2 中的所有值。
这是我的插入代码：
[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();
}
}
}
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();
}
}
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您无法阻止有选择地运行触发器，无论列如何更新，它都将始终运行。但是，一旦启动，您可以查阅
COLUMNS_UPDATED()< /a> 功能：因此，您可以调整触发逻辑，以便根据更新的列采取适当的操作。
话虽这么说，从 SQLCLR 调用 WCF 是一个非常非常糟糕的主意。从触发器调用 WCF 更糟糕。您的服务器将在生产中死亡，因为事务将阻塞/中止，等待某些 HTTP 响应通过网络爬回。更不用说，在存在回滚的情况下，您的调用本质上是不正确的，因为您无法撤消 HTTP 调用。执行此类操作的正确方法是通过队列将操作和 WCF 调用解耦。您可以使用 用作队列的表来执行此操作，您可以使用真正的队列或者您可以使用更改跟踪。其中任何一个都允许您将更改和 WCF 调用解耦，并允许您从单独的进程而不是 SQLCLR 进行调用
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: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