[dba-SQLServer] Constant Poll: Approaches

Triggers are probably out of the question because you need to process the rows 
sequentially.

Procs with Infinite loops tend to be resource hogs and it's not so easy to program complex 
business logic in TSQL.

I'd go with an external agent.

I've got a couple of similar things running.  Both use ODBC for the connection and a front 
end application or service which sits in the background and periodically polls the data base, 
applies the logic and updates where required.

I generally write this sort of thing in PowerBasic with the SQL Tools ODBC package but any 
environment which can generate small footprint executables, can access ODBC, can 
"sleep" and can spawn threads will do it.



On 10 Jun 2008 at 13:27, Arthur Fuller wrote:

> I have a pair of tables, call them Fiction and Fact. People around the world
> enter rows into Fiction. The database has to process these records and
> depending on some logic, it makes a decision whether or not to enter a
> similar row into Fact. The logic is not important to my question. What I'm
> trying to do is set up a "polling" system so that the engine will examine
> the Fiction table every 10 seconds or so and if there are any new rows, fire
> the logic that decides whether to create a row in the Fact table, and then
> timestamp the Fiction row so we know that it's been processed.
> 
> One important detail in this operation is that I cannot move to row 2 before
> completely processing row 1, because the results of row 1 may affect the
> outcome of row 2.
> 
> I can think of a couple of approaches -- agent job, trigger, proc with an
> infinite loop. But before I get started coding this, I thought that I'd
> reach out and see if anyone's done something similar and has advice on which
> approach might be best.
> 
> Thanks in advance for any suggestions.
> 
> Arthur
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>