Member 12336474 Ответов: 1

Как создать триггер SQL server для отправки электронной почты на основе условий

I have this "schedules" table which shows the operator on what job he needs to work on next based on the priority of the job. The operator must follow the table seq.

But sometimes for some reason the operator does not follow the schedule and decides to work on a different job. When the operator finishes a job the work order is moved to the next table which "completed_jobs". When the job is moved it get removed from the schedules table. Now I want to set a trigger on the complete jobs table that when it is updated, it figures out if that was the job that needed to be done.

The problem is the schedule table updates every 5 minutes. So if at 12am "JOB A" was number one priority, at 12:05 there could be a new job "JOB B" which is on number 1. But the shift starts a 12 am so the operator should work on JOB A first.

Please advice what I need to have this procedure going. I am sorry if I am asking for a lot here, I just need some advice on how to get this started.

tbl_Schedules

Dept  WO  parts panels
----------------------
1    11   10    23
2    23   32    33
1    34   34    34
2    33   44    24
completed_jobs

DEPT  WO  DATE_Completed
-------------------------
1     22   22/10/2017
2     24   22/10/2017
1     26   22/10/2017


Что я уже пробовал:

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

1 Ответов

Рейтинг:
12

Jesus Carroll

Практический подход к управлению этой работой может быть реализован через сервис-брокера. Это полезно, когда вам нужно обрабатывать некоторые связанные динамические структуры FIFO. И эта функция доступна начиная с Microsoft SQL Server 2005 ( Майкрософт Документы):

С помощью Service Broker внутренние или внешние процессы могут отправлять и получать гарантированные асинхронные сообщения с помощью расширений языка обработки данных Transact-SQL (DML). Сообщения могут быть отправлены в очередь в той же базе данных, что и отправитель, в другую базу данных в том же экземпляре SQL Server или в другой экземпляр SQL Server либо на том же сервере, либо на удаленном сервере.

В вашем случае сообщения должны быть отправлены в очередь в той же базе данных, что и отправитель и получатель. Вы можете забыть о дескрипторе приоритета, потому что одна и та же служба брокера SQL Server делает это за вас, независимо от того, является ли "задание а" приоритетом номер один, а новое задание "задание В" появляется с тем же приоритетом.

Синтаксический псевдокод, который вы можете сгенерировать с помощью шаблона брокера, может быть следующим:

DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//AWDB/1DBSample/InitiatorService]
     TO SERVICE
      N'//AWDB/1DBSample/TargetService'
     ON CONTRACT
      [//AWDB/1DBSample/SampleContract]
     WITH
         ENCRYPTION = OFF;

SELECT @RequestMsg =
       N'<RequestMsg>Message for Target service.</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [//AWDB/1DBSample/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO


А для предложений, которые могли бы выполнять выполнение заданий, аналогичны этому:

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM TargetQueue1DB
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName =
   N'//AWDB/1DBSample/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Message for Initiator service.</ReplyMsg>';
 
     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE 
          [//AWDB/1DBSample/ReplyMessage]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO


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