SQL触发器与CLR的使用

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

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

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

      

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

    

using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.Threading;

namespace TestProc
{
    public class TestClass
    {
        [Microsoft.SqlServer.Server.SqlTrigger(Name = "ELMAH_Error_Trigger", Target = "ELMAH_Error", Event = "FOR INSERT ")]
        public static void triggerInsert()
        {
            string id = "";
            DateTime dt = DateTime.Now;
            //获取插入A表的数据
            using (SqlConnection cn = new SqlConnection())
            {
                cn.ConnectionString = "context connection=true";
                cn.Open();

                using (SqlCommand cmd = cn.CreateCommand())
                {
                    cmd.CommandText = "select a.Name, a.Tctime from INSERTED a";
                    SqlDataReader dr = cmd.ExecuteReader();
                    StringBuilder sb = new StringBuilder();
                    while (dr.Read())
                    {
                        id = dr[0].ToString();
                        dt=Convert.ToDateTime(dr[1]);
                        SqlContext.Pipe.Send(id+dt.ToShortDateString()+"\r\n");
                    }
                }
            }
            
            using (SqlConnection cn1 = new SqlConnection())
            {
                cn1.ConnectionString =@"server =192.168.0.102\DATAUM; database =HR;user id = sa ;password =123456";
                cn1.Open();
                using (SqlCommand cmd1 = cn1.CreateCommand())
                {
                    cmd1.CommandText = "insert into Error Values(" + id + ",'" + dt + "')";
                    SqlContext.Pipe.Send(cmd1.CommandText + "\r\n");
                    cmd1.ExecuteNonQuery();
                }
            }
        }
    }
}

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

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

         create assembly TestProc from 'E:\Disco\TestProc\TestProc\bin\Debug\TestProc.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，单击打开属性窗口，切换到"安全"选项卡，做如图配置

           

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

           好了，至此大功告成！