Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I'm not sure about data types etc but this should at least put you on the right track. Hope it helps...</p> <pre><code>CREATE TRIGGER SendEmailOnStatusD ON JOBS -- trigger is fired when an update is made for the table FOR UPDATE --You can add the same for INSERT AS -- holds the UserID so we know which Customer was updated DECLARE @UserID int DECLARE @JobCode int SELECT @UserID = UserId, @JobCode = JobCode FROM INSERTED WHERE [Status] = 'D' --If you want the old value before the update, use 'deleted' table instead of 'inserted' table IF (@UserID IS NOT NULL) BEGIN -- holds the email DECLARE @email varchar(250) SELECT @email = Email FROM USERS WHERE UserId = @UserID EXEC SendMyEmail (@email, @jobcode); END GO </code></pre> <p><strong>EDIT</strong>:</p> <p>Above code does not handle multiple updates, so for better practice see below option </p> <pre><code>CREATE TRIGGER SendEmailOnStatusD ON JOBS -- trigger is fired when an update is made for the table FOR UPDATE --You can add the same for INSERT AS DECLARE @Updates table(UserID int, JobCode int, Email varchar(250)) INSERT INTO @Updates (UserID, JobCode, Email) SELECT i.UserID, i.JobCode, u.Email FROM INSERTED i JOIN USERS u ON i.UserID = u.UserID WHERE [Status] = 'D' DECLARE @UserID int DECLARE @JobCode int DECLARE @Email varchar(250) WHILE EXISTS(SELECT * FROM @Updates) BEGIN SELECT TOP 1 @UserID = UserID, @Email = Email, @JobCode = JobCode FROM @Updates WHERE UserID = @UserID EXEC SendMyEmail (@email, @jobcode); DELETE FROM @Updates WHERE UserID = @UserID END GO </code></pre> <p>Additionally, as discussed in the comments, sending emails from a trigger is also not the best, but as this is what the question asks for it has been included. I would recommend alternative options for sending emails such as a queue which has been mentioned in other answers.</p>
    singulars
    1. This table or related slice is empty.
    1. POCreating a status update trigger
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USPostureOfLearning
    1. USPostureOfLearning
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POCreating a status update trigger
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI am forced to criticize an answer with a trigger that can't handle multiple rows inserted at once. Sorry about that. This is NOT best practice.
      singulars
      1. PO
      1. USErikE
    2. CO@ErikE Thanks for the feedback. I see you have already added a comprehensive solution. I also agree with you that the actual sending of an email should preferably not be done from the trigger.
      singulars
      1. PO
      1. USPostureOfLearning
    3. COThanks! How about sharing the love?
      singulars
      1. PO
      1. USErikE
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload