VB.NET • Fire TestTableTrigger

Listing 4. This CLR trigger is fired on whenever an event occurs on the dbo.Test_Table. It works just like a standard database trigger, but opens up a wide range of possibilities for enhanced business logic to be placed within the trigger itself.

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes

Partial Public Class Triggers
        ' Uncomment the SqlTrigger below if you want to deploy using VS.NET
        ' <SqlTrigger(Name:="TestTableTrigger", _
        '       Target:="dbo.TestTable", Event:="FOR INSERT")> _
                Public Shared Sub TestTableTrigger()
                Dim objSqlTriggerContext As SqlTriggerContext
                Dim objSqlCommand As SqlCommand
                Dim objSqlDataReader As SqlDataReader

                ' Get the current trigger context
                objSqlTriggerContext = SqlContext.GetTriggerContext()
                Select Case objSqlTriggerContext.TriggerAction
                        Case TriggerAction.Insert
                                ' Get the list of records inserted
                                objSqlCommand = SqlContext.GetCommand()
                                objSqlCommand.CommandText = _
                                        "SELECT * FROM INSERTED"
                                objSqlDataReader = _ 
                                        objSqlCommand.ExecuteReader
                                ' Perform your business logic here

                        Case TriggerAction.Delete
                                ' Get the list of records deleted
                                objSqlCommand = SqlContext.GetCommand()
                                objSqlCommand.CommandText = _
                                        "SELECT * FROM DELETED"
                                objSqlDataReader =  _
                                        objSqlCommand.ExecuteReader
                                ' Perform your business logic here
                End Select
        End Sub
End Class