SQL Server CLR trigger instance

A problem to be solved recently is to run a workflow on an OA system. After running a workflow, you need to write the batch of form data to the HR system. I want to write a small tool for synchronization, but I have to worry about the real-time problem. The first reaction reminds me of the trigger.

I have written some triggers in SQL Server, but I often use some cursors. In short, I feel uncomfortable. I used to understand SQL Server CLR, but I haven't actually used it. I will try it this time to see how it works.

 

The implementation can be divided into the following parts:

 

1. Create an SQL server project in:

 

 

 

2. Create a trigger function class

 

 

3. Compile the code to implement the function

 

Using system; <br/> using system. data; <br/> using system. data. sqlclient; <br/> using Microsoft. sqlserver. server; </P> <p> Public partial class triggers <br/> {<br/> // enter an existing table or view for the target and uncomment the attribute row. <br/> [Microsoft. sqlserver. server. sqltrigger (name = "triggertest", target = "t_systeminfo", event = "for update, insert")] <br/> Public static void triggertest () <br/>{< br/> If (sqlcontext. triggercontext. triggeraction = triggeraction. insert) <br/>{</P> <p> // The following section describes the functions to be completed by the trigger. <br/> using (sqlconnection sqlcon = new sqlconnection ("context connection = true ")) <br/>{< br/> sqlcon. open (); <br/> sqlcommand sqlcom = sqlcon. createcommand (); <br/> sqlcom. commandtext = "select * From inserted"; <br/> datatable dt = new datatable (); <br/> sqldataadapter sqlda = new sqldataadapter (sqlcom); <br/> sqlda. fill (DT); <br/> sqlcom. commandtext = "insert into [DBO]. [Test] ([MSG]) values ('"+ dt. rows [0] ["ID"]. tostring () + dt. rows [0] ["systemname"]. tostring () + "')"; <br/> sqlcom. executenonquery (); <br/>}</P> <p >}< br/>}

 

 

4. Create an SQL CLR assembly

 

Compile the project you just created and obtain the sqlserverproject1.dll file. For demonstration convenience, I created a CLR directory under the C-drive root directory and copied the sqlserverproject1.dll file to the CLR directory.

Open CLR:

Exec sp_configure 'clr enabled', 1

 

Create an assembly

Create Assembly jomooclr from 'C:/CLR/sqlserverproject1.dll 'with permission_set = safe

 

The above red department is replaced based on the actual path

 

5. Create a trigger

 

Create trigger mytrg on t_systeminfo
For insert
As
External name jomooclr. triggers. triggertest