Error updating data in table that uses a trigger?

Hello. I am new to SQL Server Management Studio 2005 and am using it to connect to a database on another machine running SQL Server 2000. Everything seems to work well, except when I try to edit table data on a particular table that has an update trigger associated with it. I am receiving the error:

-

The data in row 1 was not committed.

Error Source: Microsoft.VisualStudio.DataTools.

Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows).

Correct the errors and retry or press ESC to chancel the changes.

--

I want it to update those the 5 rows in the other tables...that's the whole point of having the trigger!

This used to work fine from Enterprise Manager 2000 and If I issue the update query manually from the new integrated "Query Analyzer" it is also ok. Is there some way to deactivate this new feature from the table viewer?

Thanks very much!

That error message occurred once for because there was a constraint which the deletion violated. I believe I had a field set to "NOT NULL" and the deletion was attempting to delete just that field for some reason.|||Also make sure that there are not two rows where every column is exactly the same.|||

Thanks for responding. My modification is not causing duplicate records to be created, nor am I violating any of my table rules (such as not NULL).

What I have is a trigger associated with my table so that whenever a record is updated it runs a stored procedure which then updates related information in other tables. It is not an error, but a very essential function of my database.

If I issue a manual update query (with query analyzer) I receive no errors and my trigger runs perfectly. It's only when I try to update a record interactively with the "Open Table" viewer/interface that I receive the error.

This new feature is very disruptive to the way I work and I was hoping that there was an option somewhere to turn it off, or make it so that it only applies to real errors such as duplicate records being created.

Thanks....

|||Can you run Profiler and gather a trace to see what it's doing differently than QA?|||

It's not really doing anything different, it's just that the new interactive "table viewer" has some sort of error checking routine that prohibits the update from executing because it sees that the query will affect data in other tables... which is what it was designed to do and worked flawlessly with Enterprise Manager 2000.

Am I the only person here who uses triggers to update data in other tables?

Thanks...

|||

You're absolutely not... I have the same problem. Very frustrating having to run the old version of Enterprise Manager just to update a few values.

Did you manage to find a fix for this?

|||Nope. No fix yet (that I'm aware of). Maybe if we keep this thread visible someone higher up can recommend that it be addressed in the next service pack.|||

Please file a defect report with the MSDN Feedback Center (http://lab.msdn.microsoft.com/productfeedback/).

If you can include a minimal script to create sample tables and a stored procedure to set up the test, that will help quite a bit.

Thanks,
Steve

|||

This appears to be a bug in the how SMSS is interperting the ADO 'feedback' area.

I have a table (T1) with a trigger that updates another table (T2) on INSERT and UPDATE.

1. UPDATE T1 SET C1 = 'value' WHERE ID=2 works fines

2. Trapping the SSMS generated 'sp_executesql' statement using Profiler and then executing it works fine.

3. Using SSMS to update the same row results in the error above.

Note that both1 and 2 above result in messages as follows:

(1 row(s) affected)

(1 row(s) affected)

If memory serves me right these 'messages' are both passed back in the ADO 'feedback' area and can be processed by the code of SSMS. Seems the code wasn't tested with replications scenarios.

Im on W2K3 and SQL 2005 SP plus hot fixes.

|||

There is an open Microsoft Connect issue... you can track the progress here:
https://connect.microsoft.com:443/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=125566

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||I'm not sure if this will help any of you but I was able to get around this by putting a SET NOCOUNT ON at the beginning of my trigger.