November 11, 2002 at 2:02 pm
Hi,
I've got a simple insert stored procedure, however everytime a new row is inserted into the table I need to run a sometimes lenghty process that checks and update any orders that have been backordered. Is there a way to run the backorder process without affecting the preformace of the insert? In an application I would do the insert and then run the backorder process in its own thread.
Thanks for any advice
Randy
November 11, 2002 at 2:24 pm
You could use an insert trigger to put a record into a control table that holds those needing the backorder process run on them. Then a SQL server job running in a loop would be used to read the control table and make the needed calculations. The SQL server job would keep track of which records it had done already.
-Dan
-Dan
November 11, 2002 at 3:47 pm
Or even better put a marker on the row that is inserted to check for backorder items. Just needs to be a bit and requires no interaction other than a job as suggested by dj_meier. The only reason I would do as a bit field on the current table is to conserve space and eliminate the trigger which would be an extra failure point. When adding the column I would make the default 1 and column to not null to set all the previous records so that they are not touched, then change the default after adding to 0, this way you should not have to change your insert either. You job looks for 0 in the field, does the processing then sets to 1.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply