Stuart McLachlan
stuart at lexacorp.com.pg
Fri Jun 13 19:14:56 CDT 2008
If several people records are saved at about the same time, several triggers can be running at the same time. In Arthur's scenario - Row 2 could be triggering before Row 1 is finished and timestamped and vice versa. On 13 Jun 2008 at 16:03, Francisco Tapia wrote: > 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... > _______________________________________________ > dba-SQLServer mailing list > dba-SQLServer at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver > http://www.databaseadvisors.com >