Hi All,

Thanks a lot for all these prompt replies....

Scenario:

----------

There are around 30 HMIs accross 3 production plants that will fire update comfirmation on PartsManufacturing Table which is centrally located in SQL Server. It is likely that multiple updates may happen at a single point of time.

Since this confimation needs to happen in a realtime basis, we have to have an event based solution. Hence we are calling a trigger. If there is any other way out to make this scene in realtime you can even suggest that.

I can think on the following lines now... Correct me if I am wrong

1) I can use a cursor in the trigger to get all the records from the Inserted table and populate the temporary table.

(Assuming that Inserted table has only records from Partsmanufacturing Table, which means there are separate Inserted tables being created for different tables--correct me if i am wrong)

2) May be use the Service Broker to call java program and process the temporary table.

One thing I forgot to mention is that afrer the record has been processed from the temporary table I am deleting that record.

The fields of temporary table are Production Order Number & a Time Stamp. So even if there are multiple updates on the same production order they will be saved with different Time Stamp and a new entry is created.

I have tried to work with Service Broker but didnt succeed on that part, may be you ppl can guide me how to go about...

I have even tried to do some R&D on SQL Server Jobs, but again when the second trigger fires, the first Job is still running so the Job started by second trigger is aborted. The delay in the trigger was added keeping this in mind that If the previous trigger is still executing, the other will wait for some seconds and then go ahead with the execution.

I have also tried to call a Java program by passing Order Number as a comman line argument within a trigger.... but somehow the program hangs out and the query status still displays executing... The same program from command prompt with same argument works perfectly fine and the output comes within 5-6 seconds.

I am new to SQL Server 2005 so may be the code that I have written is bad perfomance wise.

Please guide me with this trigger... I am working on it madly but have not yet come to a solution...