Call dll from SQL trigger

srikrishnathanthri

Hello, I am using SQL server 2008 r2. I am new to SQL triggers and trying to call dll from SQL triggers. I have created dll using vb.net,

Public Class Class1
Public Function abc(ByVal a As String) As String
Return a.ToUpper
End Function
End Class

I have successfully added assembly to the SQL using,

CREATE ASSEMBLY triggertest
FROM 'D:\dll\triggerdll.dll'
WITH PERMISSION_SET = SAFE

and then added a trigger using,

CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.Class1.abc

Here I am getting error,

Msg 6505, Level 16, State 2, Procedure tri_add_clr, Line 1
Could not find Type 'Class1' in assembly 'triggerdll'.

where triggerdll is the name of dll. How to solve this error ? Am I calling dll from SQL trigger properly ? Please help.

Richard Deeming

VB.NET projects have a root namespace[^]. Unless you've cleared that in your project's properties, your class exists within that namespace, and you need to include it within the EXTERNAL NAME specification:

CREATE TRIGGER tri_add_clr
ON dbo.foriegn
FOR INSERT
AS
EXTERNAL NAME triggertest.[YourNamespace.Class1].abc

Also:

srikrishnathanthri wrote:

CREATE ASSEMBLY triggertest FROM 'D:\dll\Interop.ADODB.dll' WITH PERMISSION_SET = SAFE

Are you sure that's the correct assembly? That looks like an interop assembly for the ancient "ADODB" library to me.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

srikrishnathanthri

Tried your solution,

Richard Deeming wrote:

CREATE TRIGGER tri_add_clr ON dbo.foriegn FOR INSERT AS EXTERNAL NAME triggertest.[YourNamespace.Class1].abc

but I am again getting error,

Msg 6573, Level 16, State 1, Procedure tri_add_clr, Line 1
Method, property or field 'abc' of class 'ClassLibrary1.Class1' in assembly 'triggerdll' is not static.

Sorry there is a mistake in question about dll name, I have edited that.

Richard Deeming

So you need to make the function static / Shared:

Public Class Class1
Public Shared Function abc(ByVal a As String) As String
Return a.ToUpper
End Function
End Class

NB: The function you've shown will not work as a trigger. A SQL CLR trigger needs to be a Sub which uses the SqlContext.TriggerContext to perform its work. CLR Triggers[^] How to: Create and Run a SQL Server Trigger by using Common Language Run-time Integration[^] Detailed SQL Server data audit through a .NET (CLR) trigger[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer