CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlscheduled-tasks
Jaa Zaib
Jaa Zaib

Reputation: 151

How to automatically trigger a query at specific time in SQL Server

How can I run this query using auto trigger 5 minutes before the time of next prayer?

SELECT 
    DATEADD(minute, -5, (SELECT TOP 1 TIME 
                         FROM prayertimes 
                         WHERE Time > sysdatetime())) AS TIME

Here are details of the prayertimes table:

Id  P_id  TIME
---------------------------------
698  3    2016-10-08 15:31:00.000
699  4    2016-10-08 18:02:00.000
700  5    2016-10-08 19:32:00.000
701  1    2016-10-09 04:59:00.000
702  2    2016-10-09 12:08:00.000
703  3    2016-10-09 15:30:00.000
704  4    2016-10-09 18:02:00.000
705  5    2016-10-09 19:32:00.000

Let us say that next datetime is 2016-10-08 15:31:00.000.

Then I want to trigger the query automatically at 2016-10-08 15:26:00.000.

Any stored procedure?

Thanks

Upvotes: 4

Views: 26432

Answers (1)

gofr1
gofr1

Reputation: 15977

The way with job:

  1. Put your query into stored procedure,

  2. Create a job that will check interval DATEDIFF(minute,GETDATE(),<next pray time>) every minute (or 30 seconds),

  3. If interval is <= 5 minutes it will launch stored procedure execution.

The way with trigger and job.

  1. Create a trigger on update, insert, delete to keep schedule updated,

  2. Create a job that will launch your query,

  3. Trigger you create on first step will create a schedule for this job,

  4. If some changes is done to table, trigger should update the job schedule.

Upvotes: 7

Related Questions