SQL触发器与CLR的使用

　　在数据库的日常操作中，面对复杂业务的情况下，总会有用sql语句或存储过程不是那么方便的时候，所以这时候就会想到在数据库中调用CLR，也就是调用程序集，此处用C#实现来讲解一个测试案例

　　测试案例的业务是：我有两张表分别命名为A，B，当我在A表中插入一条数据时，这时我希望将插入的记录中的某些字段插入到B表中。很明显，很快我们会想到触发器，其实这个业务并不复杂，我们在一个触发器中就可以轻松实现，但是基于此篇博文的目的，我把向B表插入数据的功能放到了程序集里面，也就是我们的C#程序中。废话不多说，我们接下来就开始

1.建表，A，B。表结构如下（A,B表表结构一样，均是下面的Error表）

2.然后我们开始建程序集了，就是我们的动态库

 1 using Microsoft.SqlServer.Server;
 2 using System;
 3 using System.Collections.Generic;
 4 using System.Data.SqlClient;
 5 using System.Text;
 6 using System.Data;
 7 using System.Threading;
 8 
 9 namespace TestProc
10 {
11     public class TestClass
12     {
13         [Microsoft.SqlServer.Server.SqlTrigger(Name = "ELMAH_Error_Trigger", Target = "ELMAH_Error", Event = "FOR INSERT ")]
14         public static void triggerInsert()
15         {
16             string id = "";
17             DateTime dt = DateTime.Now;
18             //获取插入A表的数据
19             using (SqlConnection cn = new SqlConnection())
20             {
21                 cn.ConnectionString = "context connection=true";
22                 cn.Open();
23 
24                 using (SqlCommand cmd = cn.CreateCommand())
25                 {
26                     cmd.CommandText = "select a.Name, a.Tctime from INSERTED a";
27                     SqlDataReader dr = cmd.ExecuteReader();
28                     StringBuilder sb = new StringBuilder();
29                     while (dr.Read())
30                     {
31                         id = dr[0].ToString();
32                         dt=Convert.ToDateTime(dr[1]);
33                         SqlContext.Pipe.Send(id+dt.ToShortDateString()+"
");
34                     }
35                 }
36             }
37             
38             using (SqlConnection cn1 = new SqlConnection())
39             {
40                 cn1.ConnectionString =@"server =192.168.0.102DATAUM; database =HR;user id = sa ;password =123456";
41                 cn1.Open();
42                 using (SqlCommand cmd1 = cn1.CreateCommand())
43                 {
44                     cmd1.CommandText = "insert into Error Values(" + id + ",'" + dt + "')";
45                     SqlContext.Pipe.Send(cmd1.CommandText + "
");
46                     cmd1.ExecuteNonQuery();
47                 }
48             }
49         }
50     }
51 }

View Code

　　3.在SQL Server中编辑和布暑程序集，下面我将通过代码的方式进行部署

把第2部中的代码编译成动态库，然后在sql server 中执行语句

create assembly TestProc from 'E:DiscoTestProcTestProcinDebugTestProc.dll' with permission_set = unsafe --路径根据自己的情况而变动

　　　创建成功后我们就可以在左边找到我们所建的程序集

4.接着我们来创建触发器对此程序集进行调用

CREATE TRIGGER A_insert
   ON ELMAH_Error
   AFTER INSERT
AS 
    EXTERNAL NAME TestProc.[TestProc.TestClass].triggerInsert
GO

　　　5.好了，以上步骤基本上配置完了，接下来我们进行测试，向A表中插入数据时看能不能向B表中也插入相应的数据

insert into A Values("test",'2015/10/21 15:30:45')

6.可能你执行上面的插入语句会报错，那是因为在程序集中我们插入的数据库是远程机，应该对其进行相应的设置，以下是配置步骤，注意(以下配置必须同时在两个机子上进行)

　　　　a.在windows控制面版-->管理工具-->服务-->Distributed Transaction Coordinator-->属性-->启动

　　　　b.在CMD下运行"net start msdtc"开启服务后正常

c. 如果是win7,选择"管理工具下"的"组件服务",我的电脑->Distributed Transaction Coordinator->本地DTC，单击打开属性窗口，切换到"安全"选项卡，做如图配置

　　　　保存后再执行测试就可以了。

好了，至此大功告成！