How to have a database trigger code

I need some help with this..

This table I have has a date column in it, and when any of the dates in that column equal the servers date I need to tell my website/program to send out an email or perform some certain notification action t开发者_JS百科o let the user know something.

I was thinking of having a program running on the server polling the data base a certain intervals but the problem with this is if the date is 01/31/11 10:30 AM and my interval is every 5 mins there potential for the polling to be inaccurate i.e. the poll polling at 10:35 AM. In other words I need the database to somehow notify something when "x" date has been hit exactly at that date.

I'd like to avoid having a 1sec interval checking the database as I think that would be a huge performance hit.

I'm using ASP.NET MVC 3 with MSSQL and LINQ Entity framework.

Any creative ideas?

---

You could use Quartz.net to setup those events. Quartz is pretty flexible and powerful - and it was meant for this sort of thing.

---

Do not have the database trigger the code. Have a trigger create a row in another table with information about what just happened.

Have a separate program periodically read from the second table to email users or whatever you need to do. Have that program delete the row from the table once it's done with the email.

---

I don't have any personal experience, but Sql Server CLR Integration might be the answer you are looking for. From the description it sounds like you can write almost anything that will compile against the .NET framework and deploy it to a sql server instance and Sql Server will be able to execute it. http://msdn.microsoft.com/en-us/library/ms254498.aspx

---

you either need to make use of a scheduler (e.g. DBMS_SCHEDULER in Oracle or SQL Server Jobs, etc) or find some third party tool like maybe Quartz.net as mentioned by another responder. Or maybe code something like the following into a polling app

 select all jobs due in next 5 minutes, order by due date
 while there are jobs
    if the next job is due action it
    else sleep for duration of interval till job due
 loop

---

This is bit dirty, but I think it will give you the functionality you're looking for.

In Global.asax.cs

public DateTime LastMaxDateTime;

protected void Application_Start(object sender,EventArgs e)
{
    LastMaxDateTime = GetMaxDateTime();

    Thread bgThread=new Thread(BackgroundThread_CheckDatabase);
    bgThread.IsBackground=true;
    bgThread.Start();
}

private void BackgroundThread_CheckDatabase()
{
    while(true)
    {
        DateTime dtMaxDateTime = GetMaxDateTime();
        if(dtMaxDateTime > this.LastMaxDateTime)
        {
            //Send Notifications
            this.LastMaxDateTime=dtMaxDateTime;
        }

        Thread.Sleep(5000); //5 seconds
    }
}

private DateTime GetMaxDateTime()
{
     //function that returns DateTime from something like "SELECT MAX(DateTimeColumn) FROM [MyTable]"
}

Basically, the code keeps track of the newest DateTime in your table and on each poll, checks to see if there's a newer DateTime in the database since the last time it checked. If so, you can send out your notifications. If you're not expecting many records in your table that could cause a race condition, then I don't see a problem with this as a quick solution.

---

Most efficient way to do it is to have an application that instead of polling runs event-driven.

For example, have a thread query the database for the earliest scheduled event and sleep until then. Then have another thread synchronously wait for a table change (e.g. in PostgreSQL this would be the NOTIFY/LISTEN statements) and signal the first thread to check if the earliest event has changed.

---

The easiest way is to keep track of the date of your last check. When you check again, pull all rows greater than the last check date and less than or equal to the new check date. To make sure you execute them, you could add a column for when the action was performed and update that. With an index on that new column there shouldn't be any performance problem with checking it every second for rows with a NULL DateExecuted.

You could also read ahead and sort the upcoming items by trigger date and do a Thread.Wait() until the next one comes up to be precise.