速记 - 实现sql server clr trigger

本来一直反对在sql server中使用clr的，但是无奈在我之前，网站已经被人开启clr，所以今天干脆偶也来实现一个clr的trigger，用于在文章表内建立一个insert 触发器，每次发表文章，均ping一次google的博客接口。

过程如下：

1 建立sql server 项目，选择触发器

2 写代码：

public partial class Triggers
{
    // 为目标输入现有表或视图并取消对属性行的注释
    [Microsoft.SqlServer.Server.SqlTrigger (Name="Articletrigger", Target="cyzonearticle", Event="FOR INSERT")]
    public static void Articletrigger()
    {
        try
        {
            SqlTriggerContext triggContext = SqlContext.TriggerContext;
            //    SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar);

            if (triggContext.TriggerAction == TriggerAction.Insert)
            {
                using (SqlConnection conn = new SqlConnection("context connection=true"))
                {
                    conn.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    SqlPipe sqlP = SqlContext.Pipe;

                    sqlComm.Connection = conn;
                    sqlComm.CommandText = @"select i.articleid,i.userid,i.time,i.publishtype,u.username,u.blogname,u.createtime,dbo.GetLinkUrl(i.articleid,i.publishtype,u.username,i.time) as url from inserted i 
                inner join userbase u on(i.userid = u.userid)";
                    DataSet ds = new DataSet();
                    SqlDataAdapter da = new SqlDataAdapter(sqlComm);
                    da.Fill(ds);
                    DataRow dr = ds.Tables[0].Rows[0];
                    string articleid = dr["articleid"].ToString();
                    string userid = dr["userid"].ToString();
                    DateTime time = Convert.ToDateTime(dr["time"]);
                    string publishtype = dr["publishtype"].ToString();
                    string username = dr["username"].ToString();
                    DateTime createtime = Convert.ToDateTime(dr["createtime"]);
                    string blogname = dr["blogname"].ToString();
                    string url = dr["url"].ToString();
                    string rssurl = "";
                    if (publishtype.ToLower()=="a" || publishtype.ToLower()=="b" || publishtype.ToLower()=="c" )
                    {
                        rssurl = "http://www.cyzone.cn/blog/" + username + "/rss.aspx";
                    }
                    if ((time - createtime) > new TimeSpan(3, 0, 0, 0))
                    {
                        string returns = PingGoogleBlogService(blogname, url, rssurl);
                        string flag = "0";
                        if (returns != "Thanks for the ping.")
                        {
                            flag = "1";
                        }

                        sqlComm.CommandText = "INSERT X_Google_Blog_Ping_Log(articleid,time,flag) VALUES('" + articleid + "','" + time + "','" + flag + "')";
                        sqlP.Send(sqlComm.CommandText);
                        sqlP.ExecuteAndSend(sqlComm);
                    }

                }
            }
        }
        catch (Exception ee)
        { 
            System.IO.StreamWriter sw = new System.IO.StreamWriter(@"E:\services\GooglePingDll\xx.txt");
            sw.WriteLine(ee.Message+ee.Source);
            sw.Close();
        }

    }

    private static string PingGoogleBlogService(string blogname, string url, string rssurl)
    {
        WebClient w = new WebClient();
        Uri uri ;
        if (string.IsNullOrEmpty(rssurl))
            uri = new Uri("http://blogsearch.google.com/ping?name=" + blogname + "&url=" + url, false);
        else
            uri = new Uri("http://blogsearch.google.com/ping?name=" + blogname + "&url=" + url+"&changesURL="+rssurl,false);
        return w.DownloadString(uri);
    }
}

3 编译项目，上传dll到服务器指定目录

4 开启clr（偶的服务器已经默认开启）

5 USE  cyzone
  GO  
   

  EXEC   sp_changedbowner   'sa'   （更改安全主体，有些服务器不需要）

6  ALTER DATABASE cyzone SET TRUSTWORTHY ON

 7 CREATE ASSEMBLY GooleBlogPingService
FROM 'E:"services"GooglePingDll"GoogleBlogPingServiceSqlFunction.dll'
WITH PERMISSION_SET = unSAFE;

8  Create Trigger tr_aasdasd
on cyzonearticle
For INSERT
AS
External Name GooleBlogPingService.Triggers.Articletrigger
Go

其中有几点要注意的是：

 

 如果您的触发器程序中没有访问到数据库服务器的外部资源，可以将权限设置为安全模式，并且略去第5步和第6步

最后一步也是将触发器和程序集挂钩。

 这个名称 GooleBlogPingService.Triggers.Articletrigger 是比较讲究的，他的组成是：程序集名称.类名.方法名

 

that's all.