SQL triggers and the use of the CLR

In the daily operation of the database, in the face of complex business situations, always useful SQL statements or stored procedures are not so convenient, so this time will think of invoking the CLR in the database, that is, calling the assembly, here in C # implementation to explain a test case

The business of the test case is: I have two tables named A, a, a, and when I insert a piece of data in a table, I want to insert some fields from the inserted record into table B. Obviously, we will soon think of triggers, in fact, this business is not complicated, we can easily be implemented in a trigger, but based on the purpose of this blog post, I put the data into the B table in the assembly, which is our C # program. No more nonsense, we're going to start now.

1. Build a table, a, B. The table structure is as follows (A, B table structure is the following error table)

2. Then we started to build the assembly, which is our dynamic library

1 usingMicrosoft.SqlServer.Server;2 usingSystem;3 usingSystem.Collections.Generic;4 usingSystem.Data.SqlClient;5 usingSystem.Text;6 usingSystem.Data;7 usingSystem.Threading;8 9 namespaceTestProcTen { One      Public classTestClass A     { -[Microsoft.SqlServer.Server.SqlTrigger (Name ="Elmah_error_trigger", Target ="Elmah_error", Event ="For INSERT")] -          Public Static voidTriggerinsert () the         { -             stringID =""; -DateTime dt =DateTime.Now; -             //get data inserted in table a +             using(SqlConnection cn =NewSqlConnection ()) -             { +cn. ConnectionString ="Context Connection=true"; A CN. Open (); at  -                 using(SqlCommand cmd =CN. CreateCommand ()) -                 { -Cmd.commandtext ="Select A.name, a.tctime from INSERTED a"; -SqlDataReader dr =cmd. ExecuteReader (); -StringBuilder SB =NewStringBuilder (); in                      while(Dr. Read ()) -                     { toid = dr[0]. ToString (); +Dt=convert.todatetime (dr[1]); -SqlContext.Pipe.Send (Id+dt. ToShortDateString () +"\ r \ n"); the                     } *                 } $             }Panax Notoginseng              -             using(SqlConnection cn1 =NewSqlConnection ()) the             { +Cn1. ConnectionString =@"server =192.168.0.102\dataum, database =hr;user id = sa;p assword =123456"; A cn1. Open (); the                 using(SqlCommand cmd1 =cn1. CreateCommand ()) +                 { -Cmd1.commandtext ="INSERT INTO Error Values ("+ ID +", '"+ DT +"')"; $SqlContext.Pipe.Send (Cmd1.commandtext +"\ r \ n"); $ cmd1. ExecuteNonQuery (); -                 } -             } the         } -     }Wuyi}

View Code

3. Edit and set up in SQL Server, I'll deploy it in code

Compile the code in part 2nd into a dynamic library and execute the statement in SQL Server

Create ASSEMBLY TestProc from ' E:\Disco\TestProc\TestProc\bin\Debug\TestProc.dll ' with permission_set = unsafe--Path To change according to one's own circumstances

　　　Once created, we can find the assembly we built on the left.

4. Then we'll create a trigger to call this Assembly

CREATE TRIGGER A_insert on   elmah_error after   insertas     EXTERNAL NAME testproc.[ Testproc.testclass].triggerinsertgo

5. OK, the above steps are basically configured, and then we will test, to insert the data in table A to see if we can insert the corresponding data in table B

INSERT INTO A Values ("Test", ' 2015/10/21 15:30:45 ')

6. Maybe you execute the above INSERT statement will error, because in the assembly we inserted the database is a remote machine, it should be set accordingly, the following is the configuration steps, Note (The following configuration must be carried out on two machines)

　　　　A. In Windows Control Panel--Administration Tools--service-->distributed Transaction coordinator--> Properties--Start

B. Run "net start msdtc" under CMD to start the service normally

C. If it is Win7, select "Component Services" under "Administrative Tools", My Computer->distributed Transaction coordinator-> Local DTC, click Open Properties window, switch to "Security" tab, do configuration

Save and then perform the test.

All right, here we are!

SQL triggers and the use of the CLR