4

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

TT.
  • 15,774
  • 6
  • 47
  • 88
Jaa Zaib
  • 151
  • 2
  • 6
  • 14
  • 5
    Is this what your looking for http://stackoverflow.com/questions/5471080/how-to-schedule-a-job-for-sql-query-to-run-daily – Pete Oct 08 '16 at 19:06
  • 2
    Neither Windows nor SQL Server are particularly adept at running tasks at a specific millisecond. Then there are time changes, e.g. clock synchronization with trusted sources and DST, that may confuse the issue. Is forgiveness available? – HABO Oct 08 '16 at 20:09
  • Even i don't need milliseconds. I just need to Trigger it on specific Minutes only like `2016-10-08 15:26:00.000` – Jaa Zaib Oct 08 '16 at 20:45

1 Answers1

7

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.

gofr1
  • 15,741
  • 11
  • 42
  • 52