Wallace B. McClure

CLR Triggers and Objects

I spent today floundering through the the EventData and ColumnsUpdated properties in the SqlTriggerContext of Sql Server 2005 aka. Yukon. I asked a bunch of questions to a bunch of people.  Thanks to some various response to my posts in some newsgroups, I was able to understand and figure some things out.  Some of the things I learned today.

1. EventData is null and ColumnsUpdated is not null if the trigger event is a DDL operation.
2. ColumnsUpdated is null and EventData is not null if the trigger event is a DML operation.
3. SqlPipe may be called in a trigger.  I had only seen it a stored procedure

Here is my trigger code that is of significance.  Thanks to Alazel Acheson for his response in the newsgroups.

Here is the url in the Sql Server Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/302a4e4a-3172-42b6-9cc0-4a971ab49c1c.htm

    <SqlTrigger(Name:="trtblSearchResults", Target:="tblSearchResults", Event:="FOR INSERT, UPDATE")> _
    Public Shared Sub trTriggerExample()
        ' Add your code here
        Dim i As Integer = 0
        Dim sqlTrCtx As SqlTriggerContext = SqlContext.GetTriggerContext()
        Dim spPipe As SqlPipe = SqlContext.GetPipe()
        Dim sXml As SqlXml = sqlTrCtx.EventData()
        Dim strTriggerAction As String

        Try
            spPipe.Send("Total Columns Updated: " & sqlTrCtx.ColumnsUpdated.Length.ToString())
            For i = 0 To (sqlTrCtx.ColumnsUpdated.Length - 1)
                spPipe.Send("Column: " & sqlTrCtx.ColumnsUpdated(i).ToString())
            Next
            strTriggerAction = sqlTrCtx.TriggerAction.ToString()
            spPipe.Send("Trigger Action: " & strTriggerAction)
            If Not (sXml Is Nothing) Then
                If (sXml.IsNull <> True) Then
                    SqlContext.GetPipe().Send("xml data: " & sXml.Value)
                Else
                    SqlContext.GetPipe().Send("No xml data.")
                End If
            Else
                SqlContext.GetPipe().Send("EventData is nothing.")
            End If
        Catch ex As Exception
            EventLog.WriteEntry("Test trigger", ex.Message.ToString(), EventLogEntryType.Error)
        End Try
    End Sub