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)
                   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:

FROM 'C:\Users\Vivien\Documents\visual studio 2015\Projects\WriteTimeInfile\WriteTimeInfile\bin\Release\WriteTimeInfile.dll'

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

CREATE TRIGGER tri_Publishes_clr
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?

Show source
| sql-server   | sql   | triggers   | sqlclr   2017-01-02 18:01 1 Answers

Answers ( 1 )

  1. 2017-01-02 22:01

    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.

◀ Go back