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.
- EventData is null and ColumnsUpdated is not null if the trigger event is a DDL operation.
- ColumnsUpdated is null and EventData is not null if the trigger event is a DML operation.
- 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