Can I trigger events based on timestamp data or should I use polling? I'm asking this question for SQL Server 2008+ but this could really be for any DBMS.

Imagine you have scheduled data, something like:

ID   Data   Timestamp
0    NULL   2014-11-22
1    ...    2014-11-24
2    ..     2014-11-25


Is there a way to trigger my application when a timestamp is reached ?
Or shouldn't I do this in the first place and use polling instead because of performance reasons just like with triggers when new rows are added ?

I think that in theory the 'trigger based on timestamp' seems much better than the 'trigger based on new row' concerning the performance because you can easily group rows together based on their timestamps.
However, I have absolutely no idea how to actually do this in practice.

FYI: I'm inexperienced with DBMS systems, so maybe I have overlooked something very obvious. Sorry in that case.
(This is very similar to a question recently posted on S.O.: Time trigger database updating)

There is no direct way of doing this, at least not in SQL Server. In that similar question linked above I did detail a way of getting very close to this concept in SQL Server, and someone mentioned Service Broker, but these are still indirect and have not really been tested to see how well they meet up to the original desire.

On the other hand, I also mentioned in my answer in that other question that there is a technology that does do exactly this (i.e. time-based expiration): cache severs. There are at least three that I am aware of:


memcached
redis
Microsoft AppFabric




Or, depending on how this question is interpreted, if the desire is to kick off a process based on reaching a specific time, you can implement something like Quartz.NET which is a job scheduler. Using this you could still keep track of the schedules in a table in SQL Server, and when events get kicked off, part of that process would update that table in SQL Server with the status.

A simplistic, general setup might look like:


[ScheduleData] table


Add [StatusID] TINYINT


Values:


1 = Completed/Pending
2 = Running
3 = Error


Add [ErrorCount] TINYINT
Add DEFAULT CONSTRAINT on [ErrorCount] being (0)
Maybe rename [timestamp] field to be [CreatedOnDate] or [ModifiedOnDate]
Add DEFAULT CONSTRAINT on [CreatedOnDate] being (GETDATE() or GETUTCDATE())
Add [TaskOutput] NVARCHAR(MAX)
Add DEFAULT CONSTRAINT on [TaskOutput] being ('')

Stored Procedures:


[ScheduleData_AddNewTask]


Params (@Data NVARCHAR(4000 or MAX), @ScheduleDataID INT OUTPUT)
INSERT INTO dbo.ScheduleData (Data, StatusID) VALUES (@Data, 1);
SET @ScheduleDataID = SCOPE_IDENTITY();

[ScheduleData_UpdateTask]


Params (@ScheduleDataID INT, @StatusID TINYINT, @TaskOutput NVARCHAR(MAX))
UPDATE [ScheduleData]
SET StatusID = @StatusID,
TaskOutput = @TaskOutput,
ErrorCount = [ErrorCount] + (CASE @StatusID WHEN 3 THEN 1 ELSE 0 END)
WHERE ScheduleDataID = @ScheduleDataID;


App code:


When you are creating a new job to be scheduled:
First, call [ScheduleData_AddNewTask] which returns ScheduleDataID
Create the job such that it will:


Start by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 2 for @StatusID, and String.Empty for @TaskOutput.
Finish by calling [ScheduleData_UpdateTask], passing in the ScheduleDataID, 1 for @StatusID, and anything relevant for @TaskOutput.
If an error occurs, call [ScheduleData_UpdateTask], passing in the ScheduleDataID, 3 for @StatusID, and anything relevant for @TaskOutput.