February 26, 2014 at 11:31 am
Forgive what may be a stupid question; being self-taught there are huge gaps in my knowledge. I have an existing trigger in a repair order table that updates a vehicle table status field based on the changes in status to the repair order. That has been working well for many years. We have a vendor that will be maintaining their own list of vehicles, and they want me to execute a stored procedure on their server that will update their status field in a similar way. This is the last line of the existing trigger plus what I think I should add:
UPDATE Car_records SET loc_status = @CarStatus WHERE car_no = @CarNum
-- If @CarStatus = 'Released'
-- Begin
-- EXEC [BELL-CABMGR-SRV\CabManager].[CabManager].[dbo].Update_Vehicle_Shop_Status @CarNum, 1
-- End
-- Else
-- Begin
-- EXEC [BELL-CABMGR-SRV\CabManager].[CabManager].[dbo].Update_Vehicle_Shop_Status @CarNum, 0
-- End
If I run the EXEC line from SSMS it correctly updates the remote database, and I get the "1 record affected" message. Is the above the correct way to run this from a trigger, or do I have to account for the return value? Or am I completely off track? I'm not sure how to test a trigger to make sure changing it doesn't affect the existing application users.
February 26, 2014 at 12:10 pm
pbaldy (2/26/2014)
Forgive what may be a stupid question; being self-taught there are huge gaps in my knowledge. I have an existing trigger in a repair order table that updates a vehicle table status field based on the changes in status to the repair order. That has been working well for many years. We have a vendor that will be maintaining their own list of vehicles, and they want me to execute a stored procedure on their server that will update their status field in a similar way. This is the last line of the existing trigger plus what I think I should add:
UPDATE Car_records SET loc_status = @CarStatus WHERE car_no = @CarNum-- If @CarStatus = 'Released'
-- Begin
-- EXEC [BELL-CABMGR-SRV\CabManager].[CabManager].[dbo].Update_Vehicle_Shop_Status @CarNum, 1
-- End
-- Else
-- Begin
-- EXEC [BELL-CABMGR-SRV\CabManager].[CabManager].[dbo].Update_Vehicle_Shop_Status @CarNum, 0
-- End
If I run the EXEC line from SSMS it correctly updates the remote database, and I get the "1 record affected" message. Is the above the correct way to run this from a trigger, or do I have to account for the return value? Or am I completely off track? I'm not sure how to test a trigger to make sure changing it doesn't affect the existing application users.
If the code you posted above is in your trigger you need a major overhaul on your trigger. Anytime you see variables in a trigger in a sign of one of two things. Either you have a looping construct (while loop or cursor) to handle multiple rows or your trigger isn't set based and can't handle multiple rows. Either way is not good. Looping, especially in a cursor, is a performance timebomb. Not handling multiple rows in a trigger is also very bad because it will only get one value.
Can you post the entire trigger instead of just a small snippet?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2014 at 12:19 pm
Sure, the existing trigger is below. No cursor, and I can't imagine more than a single record being updated at a time. But being a novice I may have overlooked something.
USE [CarMaint]
GO
/****** Object: Trigger [dbo].[UpdateVehStatus] Script Date: 02/26/2014 08:57:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[UpdateVehStatus] ON [dbo].[tblRONumbers]
FOR INSERT, UPDATE
AS
IF Update(ROStatus)
Begin
Declare @CarNum int, @Status varchar(50), @CarStatus varchar(50), @MechBody varchar(1), @Shop varchar(10)
SELECT @CarNum = Car_no FROM inserted
SELECT @Status = ROStatus FROM inserted
SELECT @MechBody = MechBody FROM inserted
If @MechBody = 'B'
Begin
Set @Shop = 'BodyShop'
End
Else
Begin
Set @Shop = 'Shop'
End
SELECT @CarStatus = Case @Status
When 'Assigned' Then @Shop
When 'Completed' Then 'Released' --changed from Ready 10/9/09
When 'Dealer' Then 'Dealer'
When 'Dispatch' Then @Shop
When 'TA' Then 'TA'
When 'TA Insp' Then 'TA'
When 'Unassigned' Then @Shop
When 'Waiting for parts' Then @Shop
When 'Part Hold' Then @Shop
When 'Part Rec' Then @Shop
When 'PMI' Then 'PMI'
Else 'Unknown' End
UPDATE Car_records SET loc_status = @CarStatus WHERE car_no = @CarNum
February 26, 2014 at 12:37 pm
pbaldy (2/26/2014)
Sure, the existing trigger is below. No cursor, and I can't imagine more than a single record being updated at a time. But being a novice I may have overlooked something.
How about something as simple as "Change tblRONumbers so that all car numbers ending in 7 get changed". I have no idea what this table represents or the business behind it but there will be something somewhere that forces you to update or insert more than 1 row.
Consider the real possibility of needing to import data from another table. The way you have this coded it will only update 1 row in the Car_records table.
Here is how you could write this with no variables. This will work with a single row update. It will work just as well with a million row update.
ALTER TRIGGER [dbo].[UpdateVehStatus] ON [dbo].[tblRONumbers]
FOR INSERT, UPDATE
AS
IF Update(ROStatus)
UPDATE cr
SET loc_status =
case i.ROStatus
When 'Assigned' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'Completed' Then 'Released' --changed from Ready 10/9/09
When 'Dealer' Then 'Dealer'
When 'Dispatch' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'TA' Then 'TA'
When 'TA Insp' Then 'TA'
When 'Unassigned' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'Waiting for parts' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'Part Hold' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'Part Rec' Then case MechBody when 'B' then 'BodyShop' else 'Shop' end
When 'PMI' Then 'PMI'
Else 'Unknown'
End
from Car_records cr
join inserted i on cr.car_no = i.Car_no
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2014 at 1:26 pm
I appreciate the information and I'm always looking to improve. That said, it doesn't address my issue of executing a stored procedure on a linked server. I can't use the same method, as I don't have access to the tables, just the stored procedure.
February 26, 2014 at 1:34 pm
pbaldy (2/26/2014)
I appreciate the information and I'm always looking to improve. That said, it doesn't address my issue of executing a stored procedure on a linked server. I can't use the same method, as I don't have access to the tables, just the stored procedure.
Ugh, my apologies. You have a couple of options. Does this data need to be on the other server instantly or can there be a delay?
If a delay is ok I would recommend using the OUTPUT clause from the query I posted to insert the data into a staging/holding table. That will keep your trigger fast. Then you would need to create a stored proc to read the staging table row by row and call your remote proc. You could setup a job to call this proc at regular intervals depending on the needs.
If this information MUST be updated real time you would have to either call your row by row proc in your trigger or do the row by row processing inside your trigger. Since this is calling a proc on a remote server you should wrap your call inside of a try/catch block.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2014 at 1:52 pm
There could be a delay, but it would be measured in minutes rather than hours. The status field is used to let dispatchers know whether they can give the vehicle to a driver. If it goes into the shop, they need to know not to give it out. Conversely, when it becomes available, they need to know.
I will read up on the techniques you mention, as I haven't used them yet. Thanks for your help.
February 26, 2014 at 2:01 pm
pbaldy (2/26/2014)
There could be a delay, but it would be measured in minutes rather than hours. The status field is used to let dispatchers know whether they can give the vehicle to a driver. If it goes into the shop, they need to know not to give it out. Conversely, when it becomes available, they need to know.I will read up on the techniques you mention, as I haven't used them yet. Thanks for your help.
The minutes delay would be a good fit for the batch type of processing. You would need to output enough information for the remote stored proc and maybe some auditing info. Maybe as simple as a DateProcessed column or something like that so you can clean out the queue at the end of the run. This would help make it less brittle. You could do something like update that column after the remote proc runs. Then at the end of your run you could delete all rows where that column is not null.
If you run into any issues post back and we can dig deeper into your implementation and see if we can get it working for you.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 26, 2014 at 3:53 pm
Just wanted to say thanks Sean. I followed your recommendation and have a holding table populating from the trigger and a stored procedure that uses a cursor to loop the holding table and run the remote SP for each record. It also updates a field in the holding table. At the end it deletes from the holding table based on the updated field. All I have left is to schedule that SP, and I'm waiting to hear from management how often they want it to run.
February 27, 2014 at 7:24 am
pbaldy (2/26/2014)
Just wanted to say thanks Sean. I followed your recommendation and have a holding table populating from the trigger and a stored procedure that uses a cursor to loop the holding table and run the remote SP for each record. It also updates a field in the holding table. At the end it deletes from the holding table based on the updated field. All I have left is to schedule that SP, and I'm waiting to hear from management how often they want it to run.
Excellent. Sounds like a decent work around to keep your system running quickly. Glad you got it working and thanks for the update.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply