Как передать аргументы внешнему (SQLCLR) триггеру SQL Server

Question

Я создал триггер, который вызывает сборку, подобную этой:

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

Код.NET в этой сборке, который делает что-то вроде этого:

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);
        }
    }
}

Я хотел бы иметь возможность передать в качестве аргумента созданную строку или обновленную строку примерно так:

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

Я нашел 7-летнюю тему о Stackru, в которой говорится, что нет способа передать аргумент в сборку CLR.
Я спрашиваю, возможно ли это сейчас в последних версиях SQL Server.

Знаете ли вы, есть ли способ, и если да, как это сделать, пожалуйста?

Answer 1

Нет, вы не можете напрямую передавать аргументы в триггеры SQLCLR. Однако вы можете передавать значения косвенно несколькими способами (так же, как с обычными триггерами T-SQL):

1. Локальная временная таблица
2. SET CONTEXT_INFO / CONTEXT_INFO
3. В SQL Server 2016 или новее: sp_set_session_context / SESSION_CONTEXT

Во всех случаях вы получите значения, выполнив SqlCommand с выходом SqlParameter вытащить значение в код.NET. (Пожалуйста, смотрите примечание в конце относительно использования).

НО, если вы просто хотите значения inserted и / или deleted таблицы, это не были бы аргументы или параметры. Просто SELECT те, кто использует SqlCommand с помощью Context Connection = true для строки подключения и SqlDataReader, Пример этого можно увидеть на странице MSDN для триггеров CLR, в разделе Пример триггера CLR.

---

Примечание относительно передачи значений в триггеры, которые не являются частью операции DML:

Хотя это не очень распространено, безусловно, существуют допустимые варианты использования для передачи фрагмента информации из основного контекста одному или нескольким триггерам в цепочке событий. Два наиболее распространенных случая, с которыми я сталкивался: 1) передача логина на основе приложения или идентификатора пользователя (не являющегося частью SQL Server) триггеру аудита для удаления строк (поскольку эта информация не может быть добавлена ​​в столбец ModifiedBy в DELETE операция), и 2) временно отключить триггер на основе условия. И да, это возможно, и это работает. Пожалуйста, смотрите следующие мои ответы на DBA.StackExchange:

• Как обновить столбец в той же строке с триггером без рекурсии?
• Отключен или включен триггер от другого триггера

Answer 2

Псевдостолы INSERTED и DELETED всегда были доступны для прямого запроса внутри триггера SQLCLR, как показано в этой более чем 9-летней версии документации. Вы всегда можете запросить их, например:

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);
    }
}

Последние образцы одинаковы.

Вам не нужно передавать их как параметры, так же как вам не нужно передавать их как параметры обычным триггерам. Таблицы всегда доступны для запросов.

Я подозреваю, что эти таблицы не отображаются как, например, коллекции на объекте контекста, потому что это потребовало бы копирования их из буферов SQL Server (тратить впустую ЦП и память). Другая причина заключается в том, что к коллекции нельзя получить эффективный запрос. Вам придется либо использовать LINQ (используя еще больше ЦП), либо просто перебирать все содержимое (память и ЦП). Потеря памяти была бы большей проблемой, так как эта память могла бы использоваться для буферизации большего количества данных и индексов, тем самым ускоряя доступ.

Я подозреваю, что вопрос, на который вы ссылались, хотел задать то же самое, но ОП предположил, что псевдотаблицы должны передаваться в качестве параметров. Поэтому он спросил о параметрах, а не о фактической проблеме.