August 3, 2008 at 6:12 am
Hi All,
I have recently started a new job where the business application runs on SQL server (hence my involvement there!) that is written and maintained by a 3rd party.
I was asked to provide some email alerts based on certain fields being updated etc on various tables, so quickly put together some triggers (very basic ones!) and tested them on my copy of the database. They worked fine.
However when i put them onto the dev system and run the application code against it the application fails with an error which states that "Row cannot be located for updating. Some values may have been changed since it was last read"
The 3rd party first told me that i cant have an update trigger that updates the same table as the trigger is on. After a short "discussion" i got them to realise that you could.
They then tell me that i cant use triggers as they are using disconnected recordsets, which causes the code to crash, now not having any dev experiance myself i cant say whether that is right or wrong but to me it sounds a load of rubbish.
Can someone let me know if triggers are a viable option when working with disconnected recordsets please?
August 3, 2008 at 10:24 am
However when i put them onto the dev system and run the application code against it the application fails with an error which states that "Row cannot be located for updating. Some values may have been changed since it was last read"
....
They then tell me that i cant use triggers as they are using disconnected recordsets, which causes the code to crash, now not having any dev experiance myself i cant say whether that is right or wrong but to me it sounds a load of rubbish
You must realize that a disconnected recordset contains the data in the table at the time the recordset was read, any changes to the underlying data after that read is NOT transmitted to the disconnected recordset in the user application. If the user application allows the creation / deletion / updating (CUD) of table rows this information is maintained in the recordset until the application is directed to transmit the data to the underlying SQL table. Now assume a recordset row has been modified and your trigger has modified the same row - even if a column NOT modified by the user interaction with the application. Now as part of the application applying the changes to the row it retrieves this row from the table and then checks the row to determine what data the application must pass to the underlying table, as part of this checking it locates the column your trigger has altered and recognizes that the change is NOT due to the applications input. This results in the error message quoted above. Now this can be handled in the application code, but the application code to do so can be rather complex (it requires try / catch statements if the app is written in dot net code) - it can be such that the application user is notified and asked what to do, or it can abandon the change without informing the user. Being a developer I have seen too many instances of where this is ignored since the developer has the attitude of "oh well" - in other words sloppy coding coupled with a management that does not want to spend the additional time/money to do it correctly the first time. The 3rd party in this case appears to be one of trying to do it "on the cheap"
August 3, 2008 at 11:37 am
Many thanks for the detailed reply, at least now i understand why that message is appearing after the update statement from the application. I couldn't work out why the application cared what was going on after it had done the update, but now i understand.
Seems there isn't a quick way round this one for me 🙁 Problem is i either have to not implement triggers, pay for them to do the email alerts in code (quoting 4 hours for a simple "if this column changes from a to b then send mail!) or pay for them to amend the code to get round these errors.
Grr!!
Many thanks again 🙂
August 3, 2008 at 11:55 am
Animal Magic (8/3/2008)
Many thanks for the detailed reply, at least now i understand why that message is appearing after the update statement from the application. I couldn't work out why the application cared what was going on after it had done the update, but now i understand.Seems there isn't a quick way round this one for me 🙁 Problem is i either have to not implement triggers, pay for them to do the email alerts in code (quoting 4 hours for a simple "if this column changes from a to b then send mail!) or pay for them to amend the code to get round these errors.
Grr!!
Many thanks again 🙂
I guess I don't understand the problem. Why would you be updating the data in the table with a trigger in the first place? All you should be doing is checking to see if the data has been changed, and if so - add an entry to a queue table. A secondary process would look to the queue and send the appropriate email messages.
Are you actually changing data in the table?
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 3, 2008 at 12:45 pm
Jeffrey Williams (8/3/2008)
Animal Magic (8/3/2008)
Many thanks for the detailed reply, at least now i understand why that message is appearing after the update statement from the application. I couldn't work out why the application cared what was going on after it had done the update, but now i understand.Seems there isn't a quick way round this one for me 🙁 Problem is i either have to not implement triggers, pay for them to do the email alerts in code (quoting 4 hours for a simple "if this column changes from a to b then send mail!) or pay for them to amend the code to get round these errors.
Grr!!
Many thanks again 🙂
I guess I don't understand the problem. Why would you be updating the data in the table with a trigger in the first place? All you should be doing is checking to see if the data has been changed, and if so - add an entry to a queue table. A secondary process would look to the queue and send the appropriate email messages.
Are you actually changing data in the table?
Hi Jeffrey,
Ive slightly complicated matters here. there are a few areas where i have been looking to implement triggers, most are for email reasons, in which case yes i am looking for an update and then inserting a row into a queue table as you mention. The scenario mentioned above is not for an email alert, what it does is checks if the lastPaid field has been updated, and if so change the status of the member to active if they were inactive (stored in the same table).
However, both methods fail, updating the same table and inserting into a queue table. Both on update and delete triggers as well!
August 3, 2008 at 1:05 pm
Animal Magic (8/3/2008)
Hi Jeffrey,Ive slightly complicated matters here. there are a few areas where i have been looking to implement triggers, most are for email reasons, in which case yes i am looking for an update and then inserting a row into a queue table as you mention. The scenario mentioned above is not for an email alert, what it does is checks if the lastPaid field has been updated, and if so change the status of the member to active if they were inactive (stored in the same table).
However, both methods fail, updating the same table and inserting into a queue table. Both on update and delete triggers as well!
Well, that would explain why you are getting an error then. If you were only looking at it from an alert point of view - I wouldn't see a problem with it. Use an AFTER UPDATE/INSERT/DELETE trigger and update the queue.
Instead of trying to update the status - send a notification to the appropriate department so they can access the application and update the status.
This way, no data is being changed and should not affect the application - and you get the alerts you are looking for.
Another approach might be to look into building a service queue instead. This could do the monitoring you need with notifications as needed without adding any triggers. I have not done this before, so I don't know what it would take - but I think it would work.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2008 at 7:17 am
Back again,
Been doing some tests just now and i created a trigger on one of the tables (dbo.Employee), the syntax shown below
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter trigger dbo.UPTrg_StatusChanges
on dbo.Employee
AFTER Update
as
begin
SET NOCOUNT ON;
exec msdb.dbo.sp_send_dbmail @recipients = 'john.morris@xxxxxxxxx', @subject = 'trig fired'
End
GO
This was done purely as a test, as you can see all it does it sends me a mail. This works fine doing updates through SQL, but crashes the application. Its not doing any updates to anything, or writing to another table, yet still it fails!!
August 4, 2008 at 7:28 am
I had the same problem working with triggers and ADO when SET NOCOUNT ON wasn't in the procedure, but, as I see from your previous post, the instruction is there.
You have to be aware that ADO tries to update a recordset checking for updated columns: it basically runs a query similar to this:
UPDATE MyTable
SET Column1 = 'NewValue1', Column2 = 'NewValue2'
WHERE KeyValue = 'KeyValue'
AND Column1 = 'OldValue1'
AND Column2 = 'OldValue2'
To check if the optimistic lock fails, it considers @@ROWCOUNT, which may be affected by something else you do in the trigger. Try running the update through SQL and then check for @@ROWCOUNT.
Hope this helps
Gianluca
-- Gianluca Sartori
August 4, 2008 at 8:28 am
weirdly this seems to be down to the name of the trigger?? If i create exact same trigger but call it [dbo].[UPTrg_StatusChanges] it seems to work fine?!?
I will confirm this soon.
August 4, 2008 at 8:35 am
Animal Magic (8/4/2008)
weirdly this seems to be down to the name of the trigger?? If i create exact same trigger but call it [dbo].[UPTrg_StatusChanges] it seems to work fine?!?I will confirm this soon.
It doesn't make sense at all!!!:crazy:
-- Gianluca Sartori
August 5, 2008 at 11:25 am
I've had the problem with disconnected recordsets - mostly in MS Access where a form is bound to a table, the form does and update on the table, a trigger then modifies the same table and Access gives an error message or all of the fields on the form show #deleted#.
The only way I've been able to get around the problem was to dump the values I wanted - using inserted and deleted tables in a trigger - into another table. Then have a SQL Server Agent job wake up some time later and process that table. I had to cross my fingers that the same records wouldn't also be in the user application when the Agent job ran or the application would give the same error message.
There was another application written in Delphi with a similar problem and I found that it was very sensitive to anything returned from SQL Server and I had to be sure to SET NOCOUNT ON before doing anything or the application would think there was an error.
Todd Fifield
April 6, 2010 at 12:58 pm
I had a similar issue. Created the trigger with a new name, disabled the old one. New trigger worked like a charm. Deleted the old one, created it again, deleted the 'new' version. Now 'old' trigger works like a charm as well. Don't really know why, but if it works I'll take it!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply