Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
teej
Starting Member
9 Posts |
 Posted - 2005-09-22 : 07:38:07
|
| Hi, I have a trigger on a table. I have narrowed down the problem area to this:CREATE TRIGGER trUpdatePartsWithParameters ON [dbo].[OrderItemParameters] FOR UPDATEASselect * from inserted where ItemOption = 'Yes'If the table is updated with the value 'Yes' for ItemOption the trigger runs fine. If it is updated with any other value, e.g. 'No', the trigger hangs. This only happens when a record is updated from an application, it works fine if a record is updated from Query Analyzer.I have removed any code from the application that could fire the trigger a second time and this still happens.When the application eventually times out i get the following error:"Protocol error in TDS Stream". According to microsoft support this is a generic error and it explains how to get a more specific error message using the return code from a stored procedure. I don't know how to implement this with a trigger though.Any help appreciated. |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-09-22 : 10:11:22
|
| Do 2 things for us:1. (temporarily) drop the trigger, perform the update from the app and confirm that there are no problems.2. post the actual trigger code in it's entirety.EDIT:you know, a lot of my family and friends pronounce my name like your username. How did you come up with "teej"?Be One with the OptimizerTG |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-23 : 00:03:58
|
| why are you doing some select without data retrieval in a trigger?are you retrieving @@rowcount?--------------------keeping it simple... |
|
|
|
teej
Starting Member
9 Posts |
Posted - 2005-09-23 : 03:22:04
|
| TG, Have tried running app without trigger and everything runs fine. Using a 3rd party data grid to update the table but never had any problems with this grid before.Jen,Reason for the select is that it is that part of the query that causes the problem. I have tried running the trigger a number of times and it is only if i put the "when" clause in to the trigger that it causes a problem. That select statement on it's own in the trigger is enough to hang it.The full query is below:insert into ItemGroups (OrderNumber, OrderItemID, MaterialCode) select c.OrderNumber, c.OrderItemID, c.PartCode from OrderItemParameters a join inserted b on a.OrderItemID = b.OrderItemID join OrderItemParts c on a.OrderItemID = c.OrderItemID join (select distinct MaterialCode from StockMaterials where MaterialType = 'Handle') d on c.PartCode = d.MaterialCode join Parameters e on a.ParameterID = e.ParameterID where a.ItemOption = 'No' and e.Description = 'Fit Handles'Thanks |
|
|
|
teej
Starting Member
9 Posts |
Posted - 2005-09-23 : 03:29:55
|
| TG,About the name, it's just a shorter spoken version of tj, not that my name needs to be any shorter. Besides most of the other names i've picked up over the years would prob get me kicked off the forum. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-23 : 04:08:40
|
| is this the full query? are there other triggers existing on the table aside from the one mentioned?--------------------keeping it simple... |
|
|
|
teej
Starting Member
9 Posts |
Posted - 2005-09-23 : 05:19:57
|
| That's it, only one trigger. No other triggers on the table. I've run the trigger with just the select statement shown originally. That select statement on its own in the trigger causes it to hang and couldn't be firing any other triggers... |
|
|
|
teej
Starting Member
9 Posts |
Posted - 2005-09-23 : 06:47:45
|
| Think it might be an issue with the 3rd party software after...Ran a normal update query from the same place in the application and no problems.Apologies for any time wasted. |
|
|
|
|
|
|
|
|
app hangs selecting from inserted trigger records