Блог

Главная - Вопросы по программированию - Как запустить триггер после завершения выполнения SPROC?

Как запустить триггер после завершения выполнения SPROC?

#sql #sql-server #triggers

#sql #sql-сервер #триггеры

Вопрос:

Я написал триггер, который отправляет электронное письмо после выполнения вставки строки.

 ALTER TRIGGER TR_SendMailOnDataRequest
      ON DataRequest
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE 
        @DR_Id INT,
        @DR_FullName VARCHAR(200),
        @DR_Email VARCHAR(200),
        @DR_Phone VARCHAR(20),
        @UT_Name VARCHAR(50),
        @DR_UserTypeOther VARCHAR(50) = NULL,
        @D_Name VARCHAR(200),
        @DR_RequestDate DATETIME,
        @UF_LinkedFiles VARCHAR(MAX),
        @DRN_Names VARCHAR(200),
        @DR_Description VARCHAR(1200),
        @DR_CreatedOn DATETIME,
        @analystMailList VARCHAR(MAX),
        @tableHtml NVARCHAR(MAX),
        @downloadLink VARCHAR(MAX) = N'NONE'

    SELECT @DR_Id = MAX(DR_Id) FROM dbo.DataRequest

    SELECT 
        @DR_FullName = DR_FullName,
        @DR_Email = DR_Email,
        @DR_Phone = DR_Phone,
        @UT_Name = UT_Name,
        @DR_UserTypeOther = DR_UserTypeOther,
        @D_Name = D_Name,
        @DR_RequestDate = DR_RequestDate,
        @UF_LinkedFiles = UF_LinkedFiles,
        @DRN_Names = DRN_Names,
        @DR_Description = DR_Description,
        @DR_CreatedOn = DR_CreatedOn
    FROM
        dbo.FN_GetDataRequest(@DR_Id)

    SELECT @analystMailList = dbo.FN_GetAnalystsMailList()

    IF (LEN(@UF_LinkedFiles) > 0)
    BEGIN
        SET @downloadLink = N'<a href="http://localhost:8500/workrequest/index.cfm?event=downloads.indexamp;id='   CAST(@DR_Id AS VARCHAR(10))   N'amp;k='  SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('SHA', ':be9[Dcv9wF~W!?xx4JO0OXLbZ@0p4 [~z0dO|:U,OF!13^xZb')), 3, 32)   N'">Downloads</a>'
    END

    SET @tableHTML =
        N'<H1>Data Request</H1>'  
        N'<UL>'  
        N'<LI>Full Name: '   @UF_LinkedFiles   N'</LI>'  
        N'<LI>Email: '   @DR_Email   N'</LI>'  
        N'<LI>Phone: '   CAST(@DR_Phone AS VARCHAR(20))   N'</LI>'  
        N'<LI>User Type: '   @UT_Name   N'</LI>'  
        N'<LI>User Type Other: '   COALESCE(@DR_UserTypeOther, N'NONE')   N'</LI>'  
        N'<LI>Reuest Date: '   CONVERT(VARCHAR(20), @DR_RequestDate, 107)   N'</LI>'  
        N'<LI>Downloads: '   @downloadLink   N'</LI>'  
        N'</UL>';

    BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
            @profile_name = 'Example',
            @recipients = 'John Doe<jdoe@example>',
            --@recipients = @analystMailList,
            @reply_to = @DR_Email,
            @subject = 'Email Test',
            @body_format = 'HTML',
            @body = @tableHtml
    END
END
GO

Приведенный выше триггер срабатывает при выполнении операции вставки СТРОКИ в table DataRequest. После операции вставки строки я беру идентификационный элемент, сгенерированный после операции вставки, и использую его в качестве внешнего ключа, а также ВСТАВЛЯЮ другие значения в другую таблицу. Наконец, я использую значения из обеих таблиц и создаю электронное письмо для отправки.

Я не получал значения из других таблиц (например, @UF_LINK Files), поэтому я понял, что ТРИГГЕР запускается сразу после вставки в ПЕРВУЮ таблицу, но перед ВСТАВКОЙ во ВТОРУЮ таблицу, поэтому при ОТПРАВКЕ электронной ПОЧТЫ значения недоступны.

Итак, как мне убедиться, что ТРИГГЕР срабатывает только после того, как SPROC, который выполняет все операции ВСТАВКИ в нескольких таблицах, завершил транзакцию.

Вот таблица-схема — Таблицы

Комментарии:

1. Вам также нужен триггер на втором столе.

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

3. Если ваш SP выполняет insert для обеих таблиц, вы можете определить триггер в table2 вместо определения его в table1. Таким образом, он убедится, что вставка в обе таблицы была выполнена, а затем сработал триггер. Но тогда вам также нужно будет убедиться, что вставка выполняется успешно в обеих таблицах или вообще не выполняется с использованием области транзакции.

4. Это… SELECT @DR_Id = MAX(DR_Id) FROM dbo.DataRequest это вызовет у вас проблемы при вставке нескольких строк. Посмотрите в inserted таблицу. Он содержит все ваши новые данные, нет необходимости возвращаться к таблице запросов данных.

5. Кроме того, прочтите и прислушайтесь к комментарию от @JiggsJedi. Триггер «срабатывает» один раз за вставку (а не один раз за вставленную строку). msdn.microsoft.com/en-us/library/ms191300 (v=sql.105).aspx

Ответ №1:

Вместо использования триггера я включил код ОТПРАВКИ электронной ПОЧТЫ в SPROC, в который вставляются строки.

Ответ №2:

Не уверен, что это ваш случай, потому что вы не объясняете, как происходит поведение между таблицами. Но у меня был сценарий, в котором я пытался выполнить a SELECT во время серии insert, и я не мог найти строку, потому что транзакция еще не была завершена.

Что я сделал, так это создал дополнительную таблицу

 tblProgress
    id integer,
    fieldA integer,
    fieldB integer,
    fieldC integer

Итак, если у вас есть 3 таблицы TableA, TableB и TableC, каждая таблица будет иметь один INSERT триггер и будет выполнять некоторую работу, а затем получить доступ к tblProgress.

TableA создайте строку TableB и обновите таблицу.

Тогда tblProgress также будет иметь AFTER UPDATE триггер, где вы проверяете, что все 3 поля имеют NOT NULL значение

Когда у вас есть все 3 значения, вы можете отправить электронное письмо.

Метки: