Search code examples
sqlsql-servertriggerssqlclr

SQL Server: error could not find type in assembly during dll trigger creation

In Microsoft SQL Server, I want to call a dll assembly as a trigger

This is my C# code :

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WriteTimeInfile
{
    public class Program
    {
        public static void Main(string[] args)
        {
            File.AppendAllText(@"C:\Users\Vivien\Desktop\date.txt",
                   DateTime.Now.ToString() + Environment.NewLine);
        }
    }
}

This is my properties panel:

enter image description here

Then I create the assembly in SQL Server, it seems to work and create an assembly:

CREATE ASSEMBLY triggertest
FROM 'C:\Users\Vivien\Documents\visual studio 2015\Projects\WriteTimeInfile\WriteTimeInfile\bin\Release\WriteTimeInfile.dll'
WITH PERMISSION_SET = SAFE

But when I try to create the trigger, i try to do like it is explained in this link:

CREATE TRIGGER tri_Publishes_clr
ON STATION
FOR INSERT
AS EXTERNAL NAME triggertest.[WriteTimeInfile.WriteTimeInfile.Program].Main

I get an error

Could not find Type 'WriteTimeInfile.WriteTimeInfile.Program' in assembly 'WriteTimeInfile'.

Nothing works

Could you help me please?

Solution

  • You did not create a SQLCLR Trigger in your .NET code. This signature:

    public static void Main(string[] args)

    is for a Console App. You need to use the proper declaration for a SQLCLR object, which include using the SqlTrigger attribute.

    [SqlTrigger(Name = "trigger_name", Target = "[schema].[table]",
            Event = "FOR INSERT, UPDATE, DELETE")]  
public static void TriggerName()
{
}

    For more info on SQLCLR Triggers, please see the MSDN page for CLR Triggers.

    For more info on working with SQLCLR in general, please see the series I am writing on SQL Server Central (free registration is required to read content on that site): Stairway to SQLCLR.

    PLEASE NOTE: IF the only reason for doing a SQLCLR Trigger is to use File.AppendAllText, then you might be better off creating a SQLCLR UDF / Scalar Function to do the same thing and then using that Function in a regular T-SQL Trigger.

    ALSO, and this is very important, since you are doing file system related functionality, your Assembly will need to be set to EXTERNAL_ACCESS. Please do not accomplish this by setting the Database to TRUSTWORTHY ON. Please sign the Assembly (protect it with a password), then create an Asymmetric Key from that Assembly in master, then create a Login based on that Asymmetric Key, and then grant that Key-based Login EXTERNAL ACCESS ASSEMBLY. You will see this approach in the various articles I wrote in the above mentioned "Stairway to SQLCLR" series.