testclrtrigger.vb

SQL Server 业已成为最为流行的几大数据库之一

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server


Partial Public Class testCLRTrigger
    <Microsoft.SqlServer.Server.SqlTrigger(Event:="FOR INSERT", Name:="trTestCLRTrigger", Target:="S_C_SC.dbo.COURSE")> _
    Public Shared Sub trTestCLRTrigger()
        Try

            Dim lsqlTrContext As SqlTriggerContext = SqlContext.TriggerContext
            If lsqlTrContext.TriggerAction = TriggerAction.Insert Then

                Dim ConnectStr As String
                ' ConnectStr = "Data Source=DMWZ-JONEPKI;Initial Catalog=master;User ID=sa;Password=jonepki;Trusted_Connection=yes"
                ConnectStr = "Context Connection=True"
                Dim lSqlConn As New SqlConnection
                lSqlConn.ConnectionString = ConnectStr
                lSqlConn.Open()
                Dim lsqlcomm As New SqlCommand
                lsqlcomm.Connection = lSqlConn

                lsqlcomm.CommandText = " DECLARE @lCId AS INT; " & _
                                      "SELECT @lCId = Cid FROM INSERTED; " & _
                                      "INSERT COURSEINFO   VALUES(@lCId,NULL,NULL);"

                lsqlcomm.ExecuteNonQuery()



                Dim osqlpipe As Microsoft.SqlServer.Server.SqlPipe
                osqlpipe = SqlContext.Pipe()
                osqlpipe.Send("测试成功")

            End If

        Catch e As Exception
            Throw New Exception("触发器执行失败", e)
        End Try
    End Sub

End Class