October 11, 2011 at 7:22 am
I am looking at what is the best way to run stored procedures against all rows in the insert table in a trigger. There are a set of procedures that run against a record when it is saved in our ERP system. I need these to rerun when the record is scheduled to a different piece of equipment. When the record is originally saved in the ERP it can only come through 1 record at at time but multiple records can be moved between pieces of equipment at once. I currently have a loop running which I don't like but I'm not sure of a better way of doing besides recreating the stored procedures within the trigger. I am hesitant of doing that based on size. The stored procedures have 2000+ lines of code in them. Any help would be greatly appreciated.
Thanks,
Jason
IF (Select count(*) from inserted i left outer join deleted d on i.JobTaskID = d.JobTaskID where i.WorkCtrID <> d.WorkCtrID and i.WorkCtrID between 300 and 400) > 0
BEGIN
Declare @Job float
Declare @LastJob float
Set @job = 0
Set @LastJob = (Select Max(i.jobNumber) from inserted i left outer join deleted d on i.JobTaskID = d.JobTaskID where i.WorkCtrID <> d.WorkCtrID and i.WorkCtrID between 300 and 400)
While @Job <> @LastJob
BEGIN
Set @Job = (Select Min(i.jobNumber)from inserted i left outer join deleted d on i.JobTaskID = d.JobTaskID where i.WorkCtrID <> d.WorkCtrID and i.WorkCtrID between 300 and 400 and i.JobNumber > @Job)
Exec proc_AdditionalInfo @Job
Exec proc_MaterialInfo @Job
Exec Proc_Barco @Job
Exec Proc_MiscTriggers @Job
END
END
October 11, 2011 at 7:40 am
If it's that complex, a loop through the inserted table is probably the best bet.
But I can't imagine what running a 2000+ line proc on each row of a multi-row update will do to both performance and your transaction log. Or, more precisely, I can imagine and shudder at the image.
Can you post the proc? It might be something that can be modified to work more set-based.
Alternatively, can you create a version of the proc that takes a table input parameter (assuming you're actually in SQL 2008, as per the forum you posted in)? Then you can pass that into the proc and have it do all the work at once.
If so, then you'll want to refactor the single-input proc so that all it does is call the table-input proc with a single row in the table. That way, you only have to maintain code in one proc instead of two.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 11, 2011 at 8:12 am
GSquared (10/11/2011)
If it's that complex, a loop through the inserted table is probably the best bet.But I can't imagine what running a 2000+ line proc on each row of a multi-row update will do to both performance and your transaction log. Or, more precisely, I can imagine and shudder at the image.
Can you post the proc? It might be something that can be modified to work more set-based.
Alternatively, can you create a version of the proc that takes a table input parameter (assuming you're actually in SQL 2008, as per the forum you posted in)? Then you can pass that into the proc and have it do all the work at once.
If so, then you'll want to refactor the single-input proc so that all it does is call the table-input proc with a single row in the table. That way, you only have to maintain code in one proc instead of two.
Thank you for the heads up on the table input parameter. We are upgrading to 2008 in less than 2 weeks and was looking to see if there were better ways in that version to handle this. This is coming from a SQL 2000 system.
Thanks,
Jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply