samplectr.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 52 行

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class SampleCTR
    <SqlTrigger(Name:="ctr_Products", Target:="Products", Event:="FOR INSERT, UPDATE, DELETE")> _
     Public Shared Sub ctr_Products()
        Dim ctxTrigger As SqlTriggerContext
        Dim spPipe As SqlPipe = SqlContext.Pipe
        ctxTrigger = SqlContext.TriggerContext()
        Select Case ctxTrigger.TriggerAction
            'Send a message describing the action
            Case TriggerAction.Insert
                spPipe.Send("Row inserted into Products table.")
            Case TriggerAction.Delete
                spPipe.Send("Row deleted from Products table.")
            Case TriggerAction.Update
                Dim intCol As Integer
                Dim strCols As String = Nothing
                For intCol = 0 To ctxTrigger.ColumnCount - 1
                    If ctxTrigger.IsUpdatedColumn(intCol) Then
                        Select Case intCol
                            Case 6
                                strCols += "UnitsInStock, "
                            Case 7
                                strCols += "UnitsOnOrder, "
                            Case 8
                                strCols += "ReorderLevel, "
                        End Select
                    End If
                Next
                If strCols Is Nothing Then
                    spPipe.Send("Products table row updated.")
                Else
                    strCols = strCols.Substring(0, strCols.Length - 2)
                    strCols = "Products table " + strCols
                    If strCols.IndexOf(", ") > 0 Then
                        strCols += " columns updated."
                    Else
                        strCols += " column updated."
                    End If
                    spPipe.Send(strCols)
                End If
        End Select
    End Sub
End Class