Как определить вызван ли триггер действиями процедуры

Question

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

Вот код которым я пытаюсь поймать процедуру

   CREATE TRIGGER dbo.TR_PRC_CATCHER
   ON  dbo.TABLE
   FOR INSERT, DELETE, UPDATE
AS
BEGIN
IF OBJECT_NAME(@@PROCID) not like '%procedure%'
 BEGIN
    RAISERROR ('Поймал!', 16, 1) WITH SETERROR;
 END
END

правильный ли это метод, или можно решить данный вопрос лаконичнее

upd: проверил данный метод, @@procid выдает object_id самого триггера а не процедуры, которой он был вызван.

Данный вариант рабочим не является

Answer 1

нужно определить вызван ли триггер действиями хранимой процедуры

В SqlServer нет встроенных средств, которые помогли бы понять, что триггер выполняется в контексте определённой процедуры. На Feedback.Azure размещён запрос (датирующийся аж 2006 годом) на добавление функции, которая давала бы информацию о стеке вызовов. Текущий статус запроса - UNPLANNED. Поэтому в данном случае придётся что-то изобретать и, видимо, без изменений в процедуре не обойтись.

Предположим, что [Table] - таблица, на которой будет триггер.

CREATE TABLE [Table]
(
    Id int IDENTITY(1, 1) NOT NULL,
    SomeDate datetime2(0) NOT NULL,
    CONSTRAINT PK_Table PRIMARY KEY (Id)
);

Для примера я буду использовать AFTER INSERT триггер, а процедура, соответственно, будет делать INSERT в эту таблицу.

Нужно сообщить триггеру каким-либо способом, что его вызов происходит в контексте определённой процедуры.

Вариант #1 - Использование временной таблицы

Внутри процедуры создаём временную таблицу

CREATE OR ALTER PROCEDURE TestProc
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #TestProc_Context(Dummy int);

    INSERT INTO [Table] (SomeDate)
    VALUES (SYSDATETIME());
END

а в триггере проверяем её наличие и делаем (или не делаем) что-то, в зависимости от этого

CREATE OR ALTER TRIGGER Table_AfterInsert ON [Table]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#TestProc_Context') IS NOT NULL
    BEGIN
        PRINT 'In TestProc.';
        RETURN;
    END
    ELSE
    BEGIN
        PRINT 'Not in TestProc';
        RETURN;
    END;
END

Проверяем работоспособность

INSERT INTO [Table] (SomeDate)
VALUES (SYSDATETIME());
GO
EXEC TestProc;
GO

Этот вариант видится мне наименее проблемным, т.к. после выхода из процедуры, что бы ни случилось, временная таблица будет уничтожена автоматически. Если нет высоких требований к throughput, то я бы порекомендовал остановиться на этом варианте. Если требования к производительности высокие, и в этом варианте ощутимо сказывается tempdb contention, то следующий вариант будет более предпочтительным.

Вариант #2 - Использование контекста сессии

В начале процедуры устанавливаем контекст, а перед выходом сбрасываем

CREATE OR ALTER PROCEDURE TestProc
AS
BEGIN
    SET NOCOUNT ON;

    EXEC sp_set_session_context @key = N'TestProc_Context', @value = 1;

    INSERT INTO [Table] (SomeDate)
    VALUES (SYSDATETIME());

    EXEC sp_set_session_context @key = N'TestProc_Context', @value = NULL;
END

в триггере, аналогично, проверяем наличие контекста

CREATE OR ALTER TRIGGER Table_AfterInsert ON [Table]
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    IF SESSION_CONTEXT(N'TestProc_Context') IS NOT NULL
    BEGIN
        PRINT 'Not in TestProc';
        RETURN;
    END
    ELSE
    BEGIN
        PRINT 'In TestProc.';
        RETURN;
    END;
END

В первом варианте автоматическое уничтожение временной таблицы после выхода из процедуры так же автоматически сбрасывает контекст. В этом же варианте контекст сбрасывается вручную, поэтому очень важно, чтобы сброс произошёл. В реальной процедуре с более сложным кодом может потребоваться блок TRY...CATCH..., если возможно возникновение ошибок в промежутке между установкой и сбросом контекста.

Функционал sp_set_session_context и SESSION_CONTEXT доступен в SqlServer 2016 и более поздних версиях. В более ранних версиях, теоретически, можно воспользоваться SET CONTEXT_INFO и CONTEXT_INFO, но я бы этот вариант не рекомендовал, если нет уверенности, что не будет пересечений ни с какими другими процессами, которые гипотетически также могут использовать CONTEXT_INFO.

Answer 2

Если процедура создаёт или изменяет запись в таблице, чем вызывает триггер, то в эту таблицу можно добавить поле с дефолтным значением object_name(@@procid))

В этом поле запишется имя процедуры вызвавшей триггер.

БЛОГ НА HUSL

Как определить вызван ли триггер действиями процедуры

Ответы (2 шт):