How to: Create, Alter, and Remove a Trigger in Visual Basic .NET
This section describes how to create a trigger by using Visual Basic .NET.
The code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks database. The trigger sends a reminder message when the table is updated or a new record is inserted.
Creating, altering, and removing triggers
Start Visual Studio 2005.
From the File menu, select New Project. The New Project dialog box appears.
In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.
(Optional) In the Name box, type the name of the new application.
Click OK to load the Visual Basic console application template.
On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SqlEnum.dll
On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.
In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:
Imports Microsoft.SqlServer.Management.SMO Imports Microsoft.SqlServer.Management.CommonInsert the code that follows this procedure into the main program.
Run and build the application.
Example
'Connect to the local, default instance of SQL Server.
Dim mysrv As Server
mysrv = New Server
'Reference the AdventureWorks database.
Dim mydb As Database
mydb = mysrv.Databases("AdventureWorks")
'Declare a Table object variable and reference the Customer table.
Dim mytab As Table
mytab = mydb.Tables("Customer", "Sales")
'Define a Trigger object variable by supplying the parent table, schema ,and name in the constructor.
Dim tr As Trigger
tr = New Trigger(mytab, "Sales")
'Set TextMode property to False, then set other properties to define the trigger.
tr.TextMode = False
tr.Insert = True
tr.Update = True
tr.InsertOrder = Agent.ActivationOrder.First
Dim stmt As String
stmt = " RAISERROR('Notify Customer Relations',16,10) "
tr.TextBody = stmt
tr.ImplementationType = ImplementationType.TransactSql
'Create the trigger on the instance of SQL Server.
tr.Create()
'Remove the trigger.
tr.Drop()
See Also
Concepts
Creating, Altering, and Removing Triggers