Триггер аудита таблиц с идентификатором хранимой процедуры

Question

У меня есть процесс, который запускается хранимой процедурой, которая идет на несколько уровней глубиной. Может быть задействовано до 12 последующих хранимых процедур.

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

Я рассмотрел аудит таблицы с помощью триггеров и попытался использовать функцию SCOPE_IDENTITY, чтобы определить, какая хранимая процедура является виновной, но она возвращает значение NULL в мою таблицу аудита.

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

Вот мой код: я создал тестовую хранимую процедуру для обновления рассматриваемой таблицы.

    --Create test Sproc

    IF EXISTS (SELECT * FROM sys.objects (NOLOCK) WHERE name LIKE 'usp_update_pkd')
    BEGIN 
      DROP PROCEDURE usp_update_pkd
    END
    GO

    CREATE PROCEDURE usp_update_pkd
    @p_order_number VARCHAR(30)

    AS

    UPDATE t_pick_detail
    SET status =  'XXX'
    WHERE order_number = @p_order_number
    RETURN
    GO


    --Create Audit Table
    IF EXISTS (SELECT * FROM sys.objects (NOLOCK) WHERE name LIKE 't_pkd_audit')
      BEGIN 
        DROP TABLE t_pkd_audit
      END
    GO
    CREATE TABLE t_pkd_audit
    (
      order_audit_id INTEGER IDENTITY(1,1) PRIMARY KEY,
      wave_id        VARCHAR(10),
      work_type      VARCHAR(20),
      order_number   VARCHAR(20),
      sproc_id       NVARCHAR(128),
      updated_on     DATETIME
    )
    GO

      --Create Trigger
      IF EXISTS (SELECT * FROM sys.objects (NOLOCK) WHERE name LIKE 'pkd_audit_record') 
    BEGIN 
      DROP TRIGGER pkd_audit_record
    END
    GO

    CREATE TRIGGER pkd_audit_record ON t_pick_detail
    AFTER UPDATE
    AS
    BEGIN
      INSERT INTO t_pkd_audit 
      (wave_id, work_type, order_number, sproc_id, updated_on )
      SELECT DISTINCT i.wave_id, i.work_type, i.order_number, CAST(SCOPE_IDENTITY() AS VARCHAR), GETDATE() 
      FROM  t_pick_detail t 
      INNER JOIN inserted i
      ON  t.order_number = i.order_number
      AND t.line_number  = i.line_number 
    END
    GO

    --Execute test SProc
    EXEC usp_update_pkd '4045'

    --Check Results
    SELECT * FROM t_pkd_audit (NOLOCK)

--Result Set
order_audit_id|wave_id|work_type|order_number|sproc_id|updated_on
1             |NULL   |17       |4045        |NULL    |2017-06-22 00:47:52.513

Буду признателен за любую оказанную помощь.

Answer 1

Добавьте в свою таблицу аудита столбец, который будет содержать имя хранимой процедуры. Поскольку оно имеет значение по умолчанию, вам не нужно изменять триггер, но вы можете удалить sproc_id, если хотите. Значение по умолчанию будет принимать @@PROCID, хранящийся в context_info, и использовать его для получения имени хранимой процедуры.

CREATE TABLE t_pkd_audit
(
    order_audit_id INTEGER IDENTITY(1,1) PRIMARY KEY,
    wave_id        VARCHAR(10),
    work_type      VARCHAR(20),
    order_number   VARCHAR(20),
    sproc_id       NVARCHAR(128),
    updated_on     DATETIME,
    SpName [varchar](128) NULL CONSTRAINT [DF_a_hist_sourceName]  DEFAULT (object_name(CONVERT([int],CONVERT([varbinary](4),context_info()))))
)

Для каждой из 12 хранимых процедур начните с:

declare @calledBy varbinary(128) = coalesce(Context_info(),0),@proc int
select @proc = @@PROCID
set context_info @proc

Он поместит идентификатор хранимой процедуры в context_info и сохранит идентификатор родительской хранимой процедуры в переменной.

В конце каждой хранимой процедуры. Если вы используете return, то также перед каждым возвратом добавьте следующий код для обратной записи идентификатора родительской хранимой процедуры обратно в context_info.

set context_info @calledby