[dba-SQLServer] Constant Poll: Approaches

I don't see why a trigger would be out of the question.. of course it does
depend on how process intensive the loops will be, but within a trigger you
have the visibility of the new records and can also get a hold of exiting
records.  At this point you can choose to update/append, or even ignore
certain records.

--
Francisco

On Tue, Jun 10, 2008 at 3:40 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

> 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
> >
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...