Listing 2: Visual Basic Code to Create the ValidateEmail CLR Trigger

BEGIN CALLOUT A
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Imports System.Text.RegularExpressions
END CALLOUT A

Partial Public Class Triggers
BEGIN CALLOUT B
    ' Enter existing table or view for the target and uncomment the attribute line.
    <SqlTrigger(Name:="ValidateEmail", Target:="vin_EmailTest", Event:="FOR INSERT, 
UPDATE")> _
END CALLOUT B
    Public Shared Sub ValidateEmail()
        ' Add your code here.
BEGIN CALLOUT C
        Dim tcx As SqlTriggerContext
        tcx = SqlContext.GetTriggerContext()
END CALLOUT C

        If (Not tcx.ColumnsUpdated(1)) Then
            Return ' Email Column was not updated.
        End If

        ' Let's process the email addresses.
BEGIN CALLOUT D
        Dim cmd As SqlCommand
        cmd = SqlContext.GetCommand()

        cmd.CommandText = "SELECT EmailID FROM INSERTED"

        Dim rec As SqlDataRecord
        rec = cmd.ExecuteRow()
END CALLOUT D

BEGIN CALLOUT E
        ' Simple RegEx to validate email address
        ' Capitalize on the .NET Framework capability.
        If (Regex.IsMatch((rec(0).ToString()), "^([\w-]+\.)*?[\w-]+@[\w-]+\.([\w-]+\.)*?[\w]+$")) Then
            ' We validate the email ID here. Validation is successful, so we return.
            Return
END CALLOUT E
BEGIN CALLOUT F
        Else
            ' We throw an exception if we get an error.
            cmd.CommandText = "RAISERROR('Function (clr_ValidateEmail): Invalid email 
address.', 16, 1)"
            cmd.ExecuteNonQuery()
            SqlContext.GetTransaction().Rollback()
        End If
END CALLOUT F
    End Sub
End Class