Код Visual Basic для создания триггера CLR ValidateEmail
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.
     _
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 Clas

Поделитесь материалом с коллегами и друзьями