How to pass arguments to an external (SQLCLR) SQL Server trigger

Question

I have created a Trigger that calls an assembly like this:

CREATE TRIGGER Testrigger ON STATION  
FOR INSERT 
AS EXTERNAL NAME assemblytest.[WriteTimeInfile.Program].Testrigger 

The .NET code in that assembly that does something like this:

namespace WriteTimeInfile
{
    public class Program
    {
        [SqlTrigger(Name = @"Testrigger", Target = "[dbo].[STATION]", Event = "FOR INSERT, UPDATE, DELETE")]
        public static void Testrigger()
        {
            File.AppendAllText(@"C:\Users\Vivien\date.txt",
            DateTime.Now.ToString() + Environment.NewLine);
        }
    }
}

I would like to be able to pass, as an argument, the created row or the updated row something like this:

CREATE TRIGGER Testrigger ON STATION  
AFTER INSERT 
AS 
EXTERNAL NAME assemblytest.[WriteTimeInfile.Program].Testrigger (STATION.ID)

I found a 7 years old topic on StackOverflow that tells there is no way to pass an argument to a CLR assembly.
I am asking if it is now possible in recent SQL Server versions.

Do you know if there is a way and if yes how to do it please?

Answer 1

No, you cannot directly pass arguments to SQLCLR Triggers. You can, however, pass values indirectly in a few ways (same as with regular T-SQL Triggers):

1. Local temporary table
2. SET CONTEXT_INFO / CONTEXT_INFO
3. On SQL Server 2016 or newer: sp_set_session_context / SESSION_CONTEXT

In all cases you would get the values by executing a SqlCommand with an output SqlParameter to pull the value into the .NET code. (Please see note at the end regarding usage).

BUT, if you just want the values of the inserted and/or deleted tables, those wouldn't be arguments or parameters. Just SELECT those using a SqlCommand using Context Connection = true for the connection string, and a SqlDataReader. You can see an example of this in the MSDN page for CLR Triggers, in the Sample CLR Trigger section.

---

Note regarding passing values to Triggers that are not part of the DML operation:

While it is not very common to do, there are certainly valid use cases for passing a piece of information along from the main context to one or more Triggers in the chain of events. The two most common cases I have come across are: 1) passing in app-based Login or UserID (not part of SQL Server) to an audit Trigger for who deleted rows (since that info cannot be added to a ModifiedBy column in a DELETE operation), and 2) temporarily disabling a Trigger based on a condition. And yes, it is possible and it does work. Please see the following answers of mine on DBA.StackExchange:

• how to update column on same row with trigger without recursion?
• Disabled or enabled trigger from another trigger