Introduction
With the integration of the CLR with SQL Server 2005, we can create database objects using modern object-oriented languages like VB.NET.
This article is trying to explain the simple and required steps that are require starting the creation of Manage Triggers using VB.NET.
The Project
We will create a Visual Studio 2005 database project for the managed Trigger.
Creating the Database project:
Open Microsoft Visual Studio 2005 and create a SQL Server Project.
File->New->Project->Database
Figure 1.
Adding a database reference:
Now it will ask for a database reference. Add one.
Figure 2.
Add a Trigger:
Right click on the Project and add a Trigger.
Figure 3.
Figure 4.
The file Trigger1.vb:
Past following lines to the file Trigger1.vb. Make sure that the table Person exist on your database or give the name of your table instead of Person.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Partial Public Class Triggers
' Enter existing table or view for the target and uncomment the attribute line
<Microsoft.SqlServer.Server.SqlTrigger(Name:="Trigger1", Target:="Person", [Event]:="FOR UPDATE")> _
Public Shared Sub Trigger1()
' Replace with your own code
SqlContext.Pipe.Send("Trigger FIRED")
End Sub
End Class
Deploy the Trigger: Build the project and then deploy it.
Figure 5.
Test the Trigger:
Make sure the CLR is enabled with your SQL Server by running the following SQL statement.
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO
Now execute an update command on the table 'Person'. You will get a line 'Trigger FIRED'.
Figure 6.
Learned Triggering!!!!