Mega Code Archive

 

Categories / MSSQL Tutorial / CLR

 

Creating a CLR Trigger

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