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

Answer 2

The INSERTED and DELETED pseudo-tables were always available for direct querying inside a SQLCLR trigger, as shown in this 9+ years old version of the documentation. You could always query them, eg :

using (SqlConnection conn = new SqlConnection("context connection=true"))
{
    conn.Open();
    SqlCommand sqlComm = new SqlCommand();
    SqlPipe sqlP = SqlContext.Pipe;

    sqlComm.Connection = conn;
    sqlComm.CommandText = "SELECT UserName from INSERTED";

    userName.Value = sqlComm.ExecuteScalar().ToString();

    if (IsEMailAddress(userName.Value.ToString()))
    {
        sqlComm.Parameters.Add(userName);
        sqlComm.CommandText = "INSERT UsersAudit(UserName) VALUES(@username)";
        sqlP.Send(sqlComm.CommandText);
        sqlP.ExecuteAndSend(sqlComm);
    }
}

The latest samples are the same.

You don't need to pass them as parameters, just like you don't need to pass them as parameters to normal triggers. The tables are always available for querying. 

I suspect these tables aren't exposed as eg collections on a context object because that would require copying them from SQL Server's buffers (wasting CPU and memory). Another reason is that a collection can't be queried effectively. You'd either have to use LINQ (using even more CPU) or simply iterate over the entire contents (memory and CPU waste). Memory waste would be the bigger problem, as this memory could be used to buffer more data and indexes, thus speeding up access.

I suspect the question you linked to wanted to ask the same thing, but the OP assumed that the pseudo-tables had to be passed as parameters. So he asked about parameters instead of the actual issue.