SQL Server trigger calls C # CLR Stored Procedure to synchronize data between two tables

Step 1: Enable CLR

 

Sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Sp_configure 'ole automation procedures ', 1;
Go
Reconfigure;
Go

Exec sp_configure 'show advanced options', '1 ';
Go
Reconfigure;
Go
Exec sp_configure 'clr enabled', '1'
Go
Reconfigure;
-- Turn advanced options back off
Exec sp_configure 'show advanced options', '1 ';
Go

 

Ii. stored procedures written in C #. Note that no namespace is required.

 

Public class syncdll
{
[Sqlprocedure]
Public static void sync (INT Atid, string atname, string atmemo, string atupperid, string aticon, string atflow, string atflowimage)
{
System. Threading. parameterizedthreadstart start = new system. Threading. parameterizedthreadstart (syncdll. Test2 );

System. Threading. Thread thread = new system. Threading. Thread (start );
System. Collections. hashtable par = new system. Collections. hashtable ();

Par ["Atid"] = Atid;
Par ["atname"] = atname;
Par ["atmemo"] = atmemo;
Par ["atupperid"] = atupperid;
Par ["aticon"] = aticon;
Par ["atflow"] = atflow;
Par ["atflowimage"] = atflowimage;
Thread. Start (PAR );
}

Public static void Test2 (Object OBJ)
{
String strconn = "Data Source = 192.168.6.68; initial catalog = pdxzsp; uid = sa; Pwd = 123456 ;";
Sqlconnection conn = new sqlconnection (strconn );
Try
{
Hashtable par = (hashtable) OBJ;
Conn. open ();
String SQL = string. format ("insert into sa_affair_theme values ({0}, '{1}', '{2}', '{3}', '{4 }', '{5}', '{6}') ", (INT) par [" Atid "],
Par ["atname"]. tostring (), par ["atmemo"]. tostring (), par ["atupperid"]. tostring (), par ["aticon"]. tostring (), par ["atflow"]. tostring (), par ["atflowimage"]. tostring ());
Sqlcommand cmd = new sqlcommand (SQL, Conn );
Cmd. executenonquery ();
}
Catch
{
}
Finally
{
Conn. Close ();

}
}
}

 

3. Enable the logon permission. This step should be optional.

 

Alter database pdxzsp set trustworthy on;
Go

Use master
Go
Create asypolicric key sqldlldemokey
From executable file =
'D: \ study \ demo \ sqlserverproject1 \ sqlserverproject1 \ bin \ debug \ sqlserverproject1.dll'
-- Create login and grant it with external access permission
Create login sqldlllogin from asypolicric key sqldlldemokey
Grant external access assembly to sqldlllogin
Go

4. Deploy assembly

 

Use pdxzsp;
Go
If object_id ('dbo. sync') is not null
Drop proc sync;
Go

Drop Assembly sqlserverproject1
Create Assembly sqlserverproject1 from 'd: \ study \ demo \ sqlserverproject1 \ sqlserverproject1 \ bin \ debug \ sqlserverproject1.dll 'with permission_set = unsafe

 

5. Create a stored procedure

Create procedure DBO. Sync (
@ Atid int,
@ Atname nvarchar (500 ),
@ Atmemo nvarchar (3000 ),
@ Atupperid nvarchar (200 ),
@ Aticon nvarchar (200 ),
@ Atflow nvarchar (3000 ),
@ Atflowimage nvarchar (200 ))
As
External name sqlserverproject1.syncdll. sync
Go

6. Call the stored procedure in the trigger and perform synchronization in the stored procedure. A thread is opened in the stored procedure to do these things. Principle:

Insert data into the sa_affair_theme table of the local database, and then obtain the data in the trigger and pass it into the CLR stored procedure,

Then open a thread and write it to the remote database table sa_affair_theme.

 

Drop trigger [tgr_theme_insert]
Create trigger [tgr_theme_insert]
On [DBO]. [sa_affair_theme]
After insert -- insert trigger
As
Begin
Declare @ Atid int
Declare @ atname nvarchar (500)
Declare @ atmemo nvarchar (3000)
Declare @ atupperid nvarchar (200)
Declare @ aticon nvarchar (200)
Declare @ atflow nvarchar (3000)
Declare @ atflowimage nvarchar (200)

Select @ Atid = Atid, @ atname = atname, @ atmemo = atmemo, @ atupperid = atupperid, @ aticon = aticon, @ atflow = atflow, @ atflowimage = atflowimage from inserted
Exec DBO. Sync @ Atid, @ atname, @ atmemo, @ atupperid, @ aticon, @ atflow, @ atflowimage

End

 

Insert the data to see if: insert into DBO. sa_affair_theme values (2, 'A', 'bb ', 'cc', 'dd', 'ee', 'ff ')