I have a trigger in place that sends an email on insert. I am wanting to create a second email when a date field equals today. From what I can tell the only way to accomplish this would be a sql job? Creating the job looks pretty easy through management studio, but I am by no means an expert and was wondering if someone could help me get the code correct. Below is the exact code I am using for the trigger. I am also including a quick snippet of code for what needs to be searched for in the records to send emails on (one email per record found);
Select * from dbo.Tasks where DueDate=GetDate() AND CompletedDate IS NULL
/****** Object: Trigger [dbo].[t_NewTaskEmail] Script Date: 11/10/2015 5:28:47 PM ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: <Author,,Name>
-- Create date:
-- Description: <Description,,>
ALTER TRIGGER [dbo].[t_NewTaskEmail]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here DECLARE @AssignedToWorkEmail varchar(100), @CreatedByAgent varchar(101), @EmailSubject varchar(450), @EmailBody varchar(max) Select @AssignedToWorkEmail = AssignedTo.WorkEmail, @CreatedByAgent = i.Priority, @EmailSubject =isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!', @EmailBody = '
<td>Type:</td> <td><b>'+i.TaskType+'</b></td> <td>Description:</td> <td>'+i.TaskDescription+'</td> <td>Priority:</td> <td> '+i.Priority+' </td> <td>Assigned:</td><td>'+cast(i.AssignedDate as varchar(20))+'</td> <td>Due:</td><td>'+isnull(cast(i.DueDate as varchar(20)),'N/A')+'</td>
DO NOT REPLY TO THIS EMAIL. THIS IS AN AUTOMATED MESSAGE FROM AN UNMONITORED MAILBOX.
From INSERTED i left join dbo.Agents AssignedTo on i.AssignedTo =AssignedTo.AgentID left join dbo.Agents CreatedBy on i.CreatedBy=CreatedBy.AgentID
@profile_name = 'ProMeLand System',
@recipients = @AssignedToWorkEmail,
@subject = @EmailSubject,
@body = @EmailBody,
@body_format = 'HTML'