SQL Server обладает широким набором интересных функций. Одной из них является возможность реализовать передачу определенной информации в пределах одной сессии. Когда это может быть нужно? Например, с одной таблицей в базе данных работает несколько приложений и каждое вводит или изменяет данные с помощью своей хранимой процедуры, а триггер в таблице на изменение данных должен это учитывать и вести себя по-разному для всех приложений. В этом случае как раз можно использовать следующий сценарий: хранимая процедура сохраняет определенное значение в переменной сессии, а триггер считывает это значение и выполняет нужной действие в зависимости от контекста. Также эту технику можно применять при использовании вложенных триггеров (nested triggers).
Переменная сессии CONTEXT_INFO позволяет сохранять данные переменной длины размером до 128 байт. Это немного, но для большинства задач должно хватить. Чтобы установить значение переменной сессии нужно воспользоваться командой SET CONTEXT_INFO, а чтобы получить текущее значение - функцией CONTEXT_INFO(). Ну и сразу приведу небольшой пример.
| DECLARE @context_info varbinary(128) SET @context_info = CAST('MyApplicationID1' AS varbinary(128)) SET CONTEXT_INFO @context_info GO SELECT CONTEXT_INFO() SELECT CAST(CONTEXT_INFO() AS varchar(128)) GO |
Между присваиванием значения и его получением я специально поставил разделитель GO, чтобы показать, что параметр сохраняет свое значение даже между разными блоками команд.
Также переменную сессии можно посмотреть в следующих системных представлениях в столбце context_info.
- sys.dm_exec_requests
- sys.dm_exec_sessions
- sys.sysprocesses
Также можно использовать технику, когда в CONTEXT_INFO мы будем хранить не одно, а несколько значений, но в разных местах. Например, мы договариваемся, что в позиции с 1 по 20 мы храним идентификатор процесса, а 21 по 28 - дату и время. Тогда процесс формирования нашей строки будет выглядеть примерно следующим образом.
| DECLARE @process_id char(20) = 'MyProcessID2' DECLARE @date_time datetime = GETDATE() DECLARE @context_info varbinary(128) SET @context_info = CAST(@process_id AS binary(20)) + CAST(@date_time AS binary(8)) SET CONTEXT_INFO @context_info SELECT CAST(SUBSTRING(CONTEXT_INFO(), 1, 20) AS char(20)) AS process_id, CAST(SUBSTRING(CONTEXT_INFO(), 21, 8) AS datetime) AS date_time |
И еще одна небольшая деталь, о которой необходимо помнить: если вы используете CONTEXT_INFO внутри транзакции, то при откате значение не возвращается к исходному. Пример:
DECLARE @context_info varbinary(128) SET @context_info = CAST('Value before transaction' AS varbinary(128)) SET CONTEXT_INFO @context_info SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value before transaction] BEGIN TRAN SET @context_info = CAST('Value inside transaction' AS varbinary(128)) SET CONTEXT_INFO @context_info SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value inside transaction] ROLLBACK SELECT CAST(CONTEXT_INFO() AS varchar(128)) AS [Value after transaction]
Финальным результатом вернется значение, которое мы установили внутри транзакции, несмотря на то, что транзакцию мы откатили. Это нужно учитывать или можно использовать.