How to create a SQL server trigger to send email based on conditions

Question

0.00/5 (No votes)

See more:

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

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?

Posted 27-Sep-17 4:11am

Member 12336474

Updated 7-Oct-17 5:03am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jesus Carroll · Accepted Answer · 2017-10-07T05:03:00

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:

SQL

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:

SQL

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.