Error updating data in table that uses a trigger?

Hot Topic

Items that are filled in
SQL Agent Failing when trying to run my SSIS Package
Load Strange Text File
Visual Studio 2003 and SQL Server Express
How can we write C# code in sql.
SQL Server 2005 Express and ASP
Query:Source from multiple tables to a Fact table
After Installed sql express sp1, install sql sp1 error.
Import an mdb file
Visibility of a matrix column base on parameter selection

SQL Server

Instert found records into DB
Visual Basic application return connection timed out
Managing Database Mail using Microsoft.SqlServer.Management.Smo.Mail managed classes
WMI Event Watcher is not working properly
SQL Server Management Studio Express CTP will not start
Any code out there for figuring Page # of Pages per Group
full text indexing
Open the subreport in an new window .
Best practice question
Help: Multiple sub-reports creating extra blank pages on print

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!

Answer this question

Error updating data in table that uses a trigger?

  • Salomi

    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



  • pcusters

    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/



  • Randy Chapman

    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.

  • Van Vangor

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

  • Fili

    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.

  • ruds

    Also make sure that there are not two rows where every column is exactly the same.

  • Trilobite

    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


  • PETER SCHWINN

    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.

  • TGA251

    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....

  • Aseem Bansal

    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...

  • Louder

    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.


  • Error updating data in table that uses a trigger?
Answer this question