触发器1.需求数据库表添加，推送数据到WCF接口2.可行方案

1.需求数据库表添加触发器，修改，删除数据，触发器生效，推送数据数据到WCF接口

2.可行方案2种触发器，第一种创建数据库项目形式

a)创建WCF服务，发布服务

b)启用数据库CLR功能，默认是关闭状态

EXEC sp_configure 'show advanced options' , '1';goreconfigure;goEXEC sp_configure 'clr enabled' , '1'goreconfigure;-- Turn advanced options back offEXEC sp_configure 'show advanced options' , '0';go

c)设置数据库安全选项

use custdbALTER DATABASE custdb SET TRUSTWORTHY ONreconfigure

d)创建WCF触发器运行环境

CREATE ASSEMBLY SMDiagnostics from'C:WindowsMicrosoft.NETFrameworkv3.0Windows Communication FoundationSMdiagnostics.dll'with permission_set = UNSAFEGO CREATE ASSEMBLY [System.Web] from'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Web.dll'with permission_set = UNSAFEGOCREATE ASSEMBLY [System.Messaging] from'C:WindowsMicrosoft.NETFrameworkv2.0.50727System.Messaging.dll'with permission_set = UNSAFE GO

CREATE ASSEMBLY  
[System.IdentityModel] from
'C:Program Files (x86)Reference AssembliesMicrosoftFrameworkv3.0System.IdentityModel.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY  
[System.IdentityModel.Selectors] from
'C:Program Files (x86)Reference AssembliesMicrosoftFrameworkv3.0System.IdentityModel.Selectors.dll'
with permission_set = UNSAFE
GO
CREATE ASSEMBLY -- this will add service modal
[Microsoft.Transactions.Bridge] from
'C:WindowsMicrosoft.NETFrameworkv3.0Windows Communication FoundationMicrosoft.Transactions.Bridge.dll'
with permission_set = UNSAFE
GO

e)创建数据库项目

数据库–》Visual C# Sql Server数据库项目连接数据库

f)编写出发器代码

WCFTrigger.cs 代码如下using System;using Client.SQLCLRServiceReference;using Microsoft.SqlServer.Server;using System.ServiceModel;public partial class Triggers{      //本代理用来提供异步调用属性，异步处理比同步操作阻塞通道直到完成操作速度要开上好几十毫秒    public delegate void MyDelagate(String crudType);    //在本项目添加WCF服务之后，生成的客户端。   static readonly ServiceContractClient proxy = new ServiceContractClient(new WSHttpBinding(), new EndpointAddress("http://127.0.0.1:8888/WCF_CLRService"));    /// 
   /// [SqlProcedure()]将程序集中本方法的定义标记为存储过程，这样在SQL服务器上注册该方法时，就能被认出来    /// 
    ///     [SqlProcedure()]    public static void SendData(String crudType)    {        /*A very simple procedure that accepts a string parameter           based on the CRUD action performed by the          trigger. It switches based on this parameter           and calls the appropriate method on the service proxy*/        switch (crudType)        {            case "Update":                                proxy.UpdateOccured();                break;            case "Insert":                proxy.InsertOccured();                break;        }    }    /// 
    /// [SqlTrigger()]将程序集中本方法的定义标记为触发器，这样在SQL服务器上注册该方法时，就能被认出来    /// Name 属性是指我们在SQL中要生成的触发器的名字    /// Target 对应哪张表    /// Event 对应哪些事件    /// 
    [SqlTrigger(Name = "WCFTrigger",       Target = "tbCR", Event = "FOR UPDATE, INSERT")]    public static void Trigger1()    {        //获触发器的上下文        SqlTriggerContext myContext = SqlContext.TriggerContext;

        MyDelagate d;
        switch (myContext.TriggerAction)
        {
            case TriggerAction.Update:
                {
                    d = new MyDelagate(SendData);
                    //异步调用
                    d.BeginInvoke("Update", null, null);
                }
                break;
            case TriggerAction.Insert:
                {
                    d = new MyDelagate(SendData);
                    d.BeginInvoke("Insert", null, null);
                }
                break;
        }
    }
   
}

g)设置数据库全线集安全改为无限制

3.第二种方案，创建数据库脚触发器形式，创建Trigger

create trigger ts_sexon student2after insertasif exists(select * from student2 where s_sex not in('男','女'))begin raiserror ('学生性别只能为“男”或者“女”',16,1)rollback transactionendinsert into student2values('2016020203','whp','男',NULL,NULL,NULL,NULL,'20160101')	

原文地址：/kimmy/archive/2011/10/17/2215926.html:///cw_volcano/p/3560695.html