July 22, 2004 at 3:34 am
I have a Wonderware InTouch SCADA updating a column in a SQL table on completion of a task. Triggers have been put on the table which are taking around 10 secs to complete. In the mean time InTouch is waiting for the UPDATE which includes the trigger completion to happen. Not a good situation for a real time control program!
Is there anyway to make SQL server complete the UPDATE before proceeding with the triggers? ie Intouch updates the column, SQL does the UPDATE and lets InTouch know all is OK, InTouch can then carry on. SQL then fires it's triggers and completes the task.
July 23, 2004 at 2:27 am
Conceptually I am not sure there is any way to do this. After all, the point of an update trigger is to make a certain set of operations appear to be atomic with the update, so I'm not sure SQL Server would have a mechanism for allowing the update to be seen as complete before the trigger is done.
I can only recommend that you look at whether the trigger operation can be sped up, or (using code) deferred to a later time. Is it critical that the 10-second operation is performed then and there, or would it be possible to merely make a note in a 'to do' table at the time of the update, then process all the 'to do' notes at a less time-critical time, in batch maybe?
July 23, 2004 at 2:51 am
Unfortunately I have inherited this database. I would never have put any triggers in place to fire when any of the real time processes write to the database.
I have made a small modification on the trigger which seems to work. Does anyone have any comments?
The InTouch App writes a status of 9 into the column. The trigger fires but sees that the status is 9 and does not do all the data manipulation but simply starts a job with the sp_start_job stored procedure.
The job executes the SQL statement "update Works set status = 1 where status = 9"
The trigger then fires, sees that the status is now 1 and proceeds with the data manipulation.
Are there any timing considerations, such as delay for 10 seconds or something to make sure the trigger has completed before the job executes its SQL? Or will the job only be started on final completion of the trigger execution?
July 23, 2004 at 7:32 am
I'd recode the triggers to do the same thing as the sp_start_job. In general, you never want to trigger some process with a trigger because the transaction will never complete.
http://www.sqlservercentral.com/columnists/sjones/worstpracticetriggeringexternalevents.asp
July 23, 2004 at 8:23 am
The job that sp_start_job is running only changes the status from 9 to 1 (Pending complete to complete) the original trigger then does all the work. It does nothing other than check the status when the column is set to 9.
How would you recode this? I need the 2nd phase to happen after InTouch has signalled it's completion of the batch it is monitoring. I am loath to recode to much as my intention is to remove this particular database and replace it with Wonderwares InBatch and Industrial SQL Server. I just want it to work safely now and remove or minimise the dependancy InTouch has on the trigger.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply