How to create an enhanced CLR Database Trigger in SQL Server

In SQL Server 2005 and later Triggers can be more than simple T-SQL statements. You can include complete procedures and program code.
This post describes how to  create a “Common Language Runtime” Trigger on a SQL Database which fires on INSERTS into a specific table.
A CLR Trigger is a kind of stored procedure including .net compiled program code.

Step 1 (Create the .NET CLR Code)

Create a piece of code in Visual Studio or in an editor of your choice.
The sample code watches a table for inserts and starts a pwershell script with the inserted values as arguments. Of course you can do much more complex things than run a script.
You can use vb.net, c# or either c++ as source language

Save  the file under C:\cla_trigger.vb

Imports System.Data.SqlClient

Imports Microsoft.SqlServer.Server

Imports System

Imports System.IO

Imports System.Diagnostics

Public Class sqltrigger

    <SqlTrigger(Name:="SessionTrigger", Target:="<your table name>", Event:="FOR UPDATE")> _

    Public Shared Sub UserNameAudit()

        'Define SQL query result variables

        Dim susername As String

        Dim sComputername As String

        Dim sID As Integer

        'Define contect of CLR Trigger

        Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext()

        Try

            If triggContext.TriggerAction = TriggerAction.Insert Then

                Using conn As New SqlConnection("context connection=true")

                    conn.Open()

                    Dim sqlComm As New SqlCommand

                    Dim sqlP As SqlPipe = SqlContext.Pipe()

                    sqlComm.Connection = conn

                    sqlComm.CommandText = "SELECT USERNAME from INSERTED"

                    susername = sqlComm.ExecuteScalar.ToString()

                    sqlComm.CommandText = "SELECT COMPUTERNAME from INSERTED"

                    sComputername = sqlComm.ExecuteScalar.ToString()

                    sqlComm.CommandText = "SELECT ID from INSERTED"

                    sID = sqlComm.ExecuteScalar.ToString()

                    ' New ProcessStartInfo created

                    Dim proc As New ProcessStartInfo

                    proc.FileName = "C:\Windows\system32\WindowsPowershell\V1.0\powershell.exe

                    proc.Arguments = "C:\Scripts\triggeroutput.ps1" _
                    & " " & sID & " " & susername & " " & sComputername

                    proc.WindowStyle = ProcessWindowStyle.Hidden

                    ' Start the process

                    Process.Start(proc)

                End Using

            End If

        Catch ex As Exception

        End Try

    End Sub

Step 2 (compile the code to a dll (class)
C:\Windows\Microsoft.NET\Framework\v2.0.50727/vbc.exe /t:library /out:C:\CLRTrigger.dll /r:sqlaccess.dll C:\cla_trigger.vb

Step 3 (prepare the database for CLR Trigger Operations)

ALTER DATABASE <your database> SET TRUSTWORTHY ON
go
sp_configure 'clr enabled', 1
Go
RECONFIGURE with Override
Go

DROP TRIGGER InsertTrigger
DROP ASSEMBLY clrtrigger
CREATE ASSEMBLY clrtrigger
FROM 'C:\CLRTrigger.dll'
go 

CREATE TRIGGER InsertTrigger
ON <your table>
FOR INSERT
AS
EXTERNAL NAME clrtrigger.sqltrigger.UserNameAudit
go