Creating a CLR Trigger : CLR « CLR « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. CLR
3. CLR

CREATE TABLE dbo.MyAudit
(MyAuditID int
NOT NULL IDENTITY(1,1) PRIMARY KEY ,
LoadDT datetime NOT NULL)

CREATE ASSEMBLY YourTrigger
FROM 'C:\YourTrigger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
GO

CREATE TRIGGER dbo.trg_i_DWNotify
ON dbo.MyAudit AFTER INSERT
AS
EXTERNAL NAME
YourTrigger.[YourTrigger.DW_Trigger].ExportFile

INSERT dbo.MyAudit
(LoadDT)
VALUES(GETDATE())

///////////
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlTypes
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Server
Imports System.IO

Public Class DW_Trigger
    Public Shared Sub ExportFile()
        Dim DWTrigger As SqlTriggerContext
        DWTrigger = SqlContext.TriggerContext

        If (DWTrigger.TriggerAction = TriggerAction.Insert) Then
            Dim DWsw As StreamWriter = New StreamWriter("C:\test.txt")
            DWsw.WriteLine(Now())
            DWsw.Close()
        End If
    End Sub
End Class