SQL Queue, execute SP at a future date

Question

I am using SQL Server 2005...

I want to monitor table1 with a trigger for an update. On the occurrence of this update, I will check another table (table2), using the data from table2 I place an item in the queue to execute at a variable date in the future. Could be 10 seconds from now, or 2 hours, this date is determined from checking the data in table2. There is a chance the trigger for table1 could execute again before the item that was placed in the queue is processed. In this case, the item in the queue needs to be removed and a new item will be placed in the queue.

Process:

table1 is updated table1's trigger fires and queries table2 to determine a timestamp. This timestamp and code are added to the queue. Something monitors this queue and executes the code at the determined timestamp. If table1 is updated before the item is retrieved from the queue, that item is removed and a new item added to the queue.

Is there is a messaging service out there that will monitor the a date and execute on that date or should I create a new sql job everytime this trigger is fired? Or are there other options? This needs to be scalable.

I hope I was able to explain my problem, if you have any questions let me know. Thanks for any help you can offer.

Answer 1

Have you looked at SQL Server Service Broker?

Service Broker Tutorials

UPDATE: You can set message priority, but I don't think you can natively extract by a specific date. Except that by definition, if you place two messages into a queue the oldest will 'pop' off the queue first, i.e. they are removed in ascending date order.

Answer 2

SQL-Server agent jobs are normally used to execute tasks at a specific date/time. I think it is possible to have a job created or modified with sp's, so you could use these in the trigger. On the down side, there may be some permission issues. The user who fires the trigger must have the permission to create/modify the job.