trigger - date in a table cell has arrived

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

trigger - date in a table cell has arrived

Author

Topic

elic05
Yak Posting Veteran

62 Posts

Posted - 2011-12-18 : 04:27:07

I have a table, one of its column is voteUntil.
its the date and time in the future that voting is disabled.

when the voteUntil = getDate()
I want to send mail

I know to create triggers that send mail when line inserted deleted or updated.
but
is it possible to trigger upon time arrived?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 04:38:10

you can use a procedure for this. just add a sql server agent job to call this procedure and inside procedure add a logic like below


CREATE PROCEDURE SentVotingMail
AS
DECLARE @Voting_Receipients Table
(
ID int IDENTITY(1,1)
... columns
)
INSERT @Voting_Receipients (Other columns except ID here...)
SELECT columns
FROM table
WHERE voteUntil >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND voteUntil < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

DECLARE @ID int,@Recipient_Email varchar(100)

SELECT @ID=MIN(ID)
FROM @Voting_Receipients

WHILE @ID IS NOT NULL
BEGIN
SELECT @Recipient_Email =Recipient_Email 
FROM Table
WHERE ID = @ID

EXEC sp_send_dbmail ...

SELECT @ID=MIN(ID)
FROM @Voting_Receipients
WHERE ID > @ID
END


GO

Im assuming you've recipient details stored in same table. If not, make sure you put correct column table names for getting the values.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 04:39:25

see this for syntax on sp_send_dbmail
http://technet.microsoft.com/en-us/library/ms190307.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

elic05
Yak Posting Veteran

62 Posts

Posted - 2011-12-18 : 04:50:03

great, thanks...
so quick, so clear...
wonderfull

elic05
Yak Posting Veteran

62 Posts

Posted - 2011-12-18 : 05:03:36

It seems that I dont have an option to
add a sql server agent job
to the remote sql server (it is hosted by hostGators)
is there another option?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-18 : 05:05:36

quote:
Originally posted by elic05

It seems that I dont have an option to
add a sql server agent job
to the remote sql server (it is hosted by hostGators)
is there another option?

you can still add the procedure but only thing is you need to run it manually if you cant create sql agent job

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources