5

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?

Community
  • 1
  • 1
Pipo
  • 5,170
  • 7
  • 33
  • 66
  • You always had access to the INSERTED and DELETED tables from inside a SQLCLR trigger, [as shown in this old version of the documendation](https://msdn.microsoft.com/en-us/library/938d9dz2(v=vs.90).aspx). You don't need to pass any arguments to the trigger, just as you don't need to pass any arguments to a normal trigger – Panagiotis Kanavos Jan 03 '17 at 16:03
  • *Why* do you want to pass parameters to the triggers? What kind of parameters? Why don't you use a stored procedure instead? If you want to implement some kind of ETL functionality with change detection, you could use Change tracking (not CDC) to find the keys and change type of modified rows and process them – Panagiotis Kanavos Jan 03 '17 at 16:32

2 Answers2

3

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:

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • Trying to use triggers as if they were stored procedures probably means they shouldn't be triggers in the first place – Panagiotis Kanavos Jan 03 '17 at 16:29
  • @PanagiotisKanavos If this were a case of trying to use Triggers as if they were Stored Procedures then I would agree with you :-). But there are definitely cases that are using Triggers as they should be used that still, on occasion, run into this need. – Solomon Rutzky Jan 03 '17 at 16:54
  • 1
    @srutzky thank you, you are becoming my official problem solver – Pipo Jan 03 '17 at 19:09
1

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.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Thank you, it is good to know but I would like to be able to pass param from updated row or any param as well, – Pipo Jan 03 '17 at 16:23
  • You can't do that with any trigger anyway because it doesn't make sense. Triggers are executed by the server so you can't manipulate values outside of them. As for the **rows** (plural), these are already available. *Plural* is the important part. They can be one or one million rows in that INSERTED or DELETED table. The syntax changes, but the ways you access the tables remains the same – Panagiotis Kanavos Jan 03 '17 at 16:26
  • @VivienPipo and Panagiotis: 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) 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, as I show in my answer, it is possible and it does work :-). – Solomon Rutzky Jan 03 '17 at 16:32
  • @srutzky better yet, *avoid* such triggers entirely and use stored procedures where appropriate. Use that Login ID in the INSERT/UPDATE statement itself. – Panagiotis Kanavos Jan 03 '17 at 16:33
  • @PanagiotisKanavos Please re-read what I wrote. In that example, it is an audit trigger (not something that can be _reliably_ moved to stored procedures), and it concerns `DELETE` statements specifically, not INSERT or UPDATE. You cannot add values to rows as they are being deleted. Also, sometimes you have ETL triggers in place that are not long-term, but needed for a migration, and you might need to disable them temporarily for various reasons. I have worked at places that ran into both situations, and it is clear from questions on S.O. and DBA.SE that others also run into these situations. – Solomon Rutzky Jan 03 '17 at 16:52
  • @srutzky I've worked in places where they didn't even have foreign keys. That doesn't make it acceptable – Panagiotis Kanavos Jan 03 '17 at 17:01