如何创建SQL Server触发器以根据条件发送电子邮件 [英] How to create a SQL server trigger to send email based on conditions

问题描述

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表更新时，它将在新表中查找相同的作业。如果不匹配则会发送电子邮件。但如果它匹配，它应该删除该部门的最高职位并从日程表中获取下一个作业。可以用触发器完成所有这些吗？

What I have tried:

I was thinking of creating a new table which will store all the top jobs for each dept. then when the completed_jobs table is updates it will look for the same job in the new table. If it doesn't match it will send an email. But if it matches it should delete the top job of that dept and grab the next job from the schedules table. Can all this be done with the trigger though?

推荐答案

可以通过Service Broker执行此Job Management的实用方法。当您需要处理一些相关的动态FIFO结构时，它非常有用。这是自Microsoft SQL Server 2005以来的一项功能（ Microsoft Technet Docs ）：



使用Service Broker，内部或外部进程可以通过使用Transact-SQL数据操作的扩展来发送和接收有保证的异步消息语言（DML）。可以将消息发送到与发送方相同的数据库中的队列，发送到同一SQL Server实例中的另一个数据库，或发送到同一服务器或远程服务器上的另一个SQL Server实例。



在您的情况下，必须将消息发送到与发送方和接收方本身相同的数据库中的队列。您可以忘记优先级句柄，因为相同的SQL Server Broker Service会为您执行此操作，无论JOB A是否为第一优先级，并且新作业JOB B以相同的优先级显示。



您可以使用Broker模板生成的语法伪代码如下：

A practical approach for this Job Management could be performed thru a Service Broker. It is useful when you need handle some related dynamic FIFO structures. And it's a feature available since Microsoft SQL Server 2005 ( Microsoft Technet Docs):

With Service Broker, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server.

In your case, the messages must be sent to a queue in the same database as the sender and receiver itself. You can forget the priority handle because the same SQL Server Broker Service does it for you, no matter if "JOB A" is number one priority, and the new job "JOB B" appears with the same priority.

A syntactical pseudo-code that you can generate with Broker template could be as follows:

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

对于可执行以下操作的句子执行工作与此类似：

And for the sentences that could perform the execution of jobs are similar to this one:

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

我希望这种方法有用。它适用于许多具有类似要求的客户。

I hope for this approach can be useful. It have works for many customers with similar requirements.

这篇关于如何创建SQL Server触发器以根据条件发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！