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
@AssignedToWorkEmail = AssignedTo.WorkEmail,
@CreatedByAgent = i.Priority,
@EmailSubject =isnull(CreatedBy.Agent,'N/A')+' assigned you a new task!',
@EmailBody = '
<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'
Nov 11, 2015 at 12:52 AM