using System;

using System.Data;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;

using System.Web.Services;

using System.Net.Sockets;

using System.Threading;

using System.Net;

using System.Text;

using System.Collections.Generic;

using System.Runtime.Remoting.Contexts;

public partial class Triggers

{

// 为目标输入现有表或视图并取消对特性行的注释

[Microsoft.SqlServer.Server.SqlTrigger(Name = "Trigger1", Target = "Users", Event = "AFTER INSERT, UPDATE")]

public static void Trigger1()

{

SqlContext.Pipe.Send("hehuajun");

List socketPool = new List();

bool firstTime = true;

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 UserName from INSERTED";

userName.Value = sqlComm.ExecuteScalar().ToString();

sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES('" + userName + "')";

sqlP.Send(sqlComm.CommandText);

sqlP.ExecuteAndSend(sqlComm);

Socket socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp);

IPEndPoint ipep = new IPEndPoint(IPAddress.Parse("192.168.10.11"), 4502); //填写自己电脑的IP或者其他电脑的IP，如果是其他电脑IP的话需将ConsoleApplication_socketServer工程放在对应的电脑上。

SqlServerProjectDemo.TableNotifyProtocol p = new SqlServerProjectDemo.TableNotifyProtocol();

p.TableName = "Users";

p.OperatorOnTable = "Update";

socket.Connect(ipep);

socket.Send(p.TableNotifyProtocolToBytes());

socket.Close();

}

}

}

//static Socket serverSocket;

// static Socket clientSocket;

// static Thread thread;

//public static void StartSocket()

//{

// IPEndPoint ipep = new IPEndPoint(IPAddress.Any, 4530);

// serverSocket = new Socket(ipep.AddressFamily, SocketType.Stream, ProtocolType.Tcp);

// serverSocket.Bind(ipep);

// serverSocket.Listen(10);

// while (true)

// {

// Socket s = serverSocket.Accept();

// socketPool.Add(s);

// // clientSocket = s;

// // thread = new Thread(new ThreadStart(doWork));

// thread = new Thread(new ThreadStart(() =>

// {

// doWork(s, ASCIIEncoding.ASCII.GetBytes("1"));

// }));

// thread.Start();

// }

//}

//public static void doWork(Socket s, byte[] buffer)

//{

// // Socket s = clientSocket;//客户端信息

// IPEndPoint ipEndPoint = (IPEndPoint)s.RemoteEndPoint;

// String address = ipEndPoint.Address.ToString();

// String port = ipEndPoint.Port.ToString();

// Console.WriteLine(address + ":" + port + " 连接过来了");

// Byte[] inBuffer = new Byte[1024];

// Byte[] outBuffer = new Byte[1024];

// String inBufferStr;

// String outBufferStr;

// try

// {

// while (true)

// {

// // s.Receive(inBuffer, 1024, SocketFlags.None);//如果接收的消息为空 阻塞 当前循环

// // inBufferStr = Encoding.ASCII.GetString(inBuffer);

// // Console.WriteLine(address + ":" + port + "说:");

// // Console.WriteLine(inBufferStr);

// //// outBufferStr = Console.ReadLine();

// // //outBufferStr = p.TableNotifyProtocolToBytes();

// // //outBuffer = Encoding.ASCII.GetBytes(outBufferStr);

// // outBuffer = p.TableNotifyProtocolToBytes();

// s.Send(buffer, buffer.Length, SocketFlags.None);

// }

// }

// catch

// {

// Console.WriteLine("客户端已关闭！");

// }

//}

public bool IsEMailAddress(string s)

{

return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$");

}

}

USE master

CREATE ASYMMETRIC KEY SQLCLRTestKey1

FROM EXECUTABLE FILE = 'C:\SqlServerProjectDemo.dll'

CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKey

GRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin;

GO

insert into Users(UserName,Pass) values('admin','admin')

Update Users set UserName='哈哈哈' where UserName='admin'

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace SqlServerProjectDemo

{

public struct TableNotifyProtocol

{

public string TableName;

public string OperatorOnTable;

public byte[] TableNotifyProtocolToBytes()

{

string s = "TableName=" + TableName + "|" + "OperatorOnTable=" + OperatorOnTable+"|";

byte[] bTableName = ASCIIEncoding.ASCII.GetBytes(TableName);

byte[] bOperatorOnTable = ASCIIEncoding.ASCII.GetBytes(OperatorOnTable);

//byte[] buffer = new byte[bTableName.Length + bOperatorOnTable.Length];

//ASCIIEncoding.ASCII.GetBytes(TableName, 0, bTableName.Length, buffer, 0);

//ASCIIEncoding.ASCII.GetBytes(OperatorOnTable, 0, bOperatorOnTable.Length, buffer, bTableName.Length);

byte[] buffer = ASCIIEncoding.ASCII.GetBytes(s);

return buffer;

}

public TableNotifyProtocol GetModel(byte[] buffer)

{

string s = ASCIIEncoding.ASCII.GetString(buffer);

TableNotifyProtocol model = new TableNotifyProtocol();

model.OperatorOnTable = GetString(s, "OperatorOnTable");

model.TableName = GetString(s, "TableName");

return model;

}

private string GetString(string s,string key)

{

string tmp = s.Substring(s.IndexOf(key) + key.Length + 1);

string v=tmp.Substring(0,tmp.IndexOf("|"));

return v;

}

}

}