November 22, 2010 at 1:39 pm
I have a Table A. When I update Table A, I want to insert a record into Table B. Table B looks very simliar to table A with a few more columns added. I am using it to export data later on. I just set this up so Table B has no rows.
I wrote an after update trigger on Table A. When I tab off of the field I changed to see if the trigger worked, I get the following message:
No row was updated
The data in row 1 was not committed.
Microsoft.VisualStudio.DataTools
The Row values updated or deleted either do not make the row unique or they alter multiple rows.
I'm changing a date field on table A from something like 12/11/2004 8:00:00 PM to 12/11/2004 8:00:17 PM. So, I'm failry certain Table A is not getting a key violation. I tried modifiying other fields as weel with the same result. The update trigger does a simply insert into table B. Why might I be getting this message?
November 22, 2010 at 1:52 pm
Rog Saber (11/22/2010)
I wrote an after update trigger on Table A. When I tab off of the field I changed to see if the trigger worked, I get the following message:
Tab off where, in SSMS? To confirm, you're using the object explorer, rt-clicking on the table, and choosing Open Table?
No row was updated
The data in row 1 was not committed.
Microsoft.VisualStudio.DataTools
The Row values updated or deleted either do not make the row unique or they alter multiple rows.
I'm changing a date field on table A from something like 12/11/2004 8:00:00 PM to 12/11/2004 8:00:17 PM. So, I'm failry certain Table A is not getting a key violation. I tried modifiying other fields as weel with the same result. The update trigger does a simply insert into table B. Why might I be getting this message?
This is very difficult to troubleshoot without seeing the DDL for the two tables and the Trigger's code.
Does performing the same action via a query in SSMS (UPDATE tableA SET field3 = SoemthingElse) also give the same, or similar, error?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 1:53 pm
Hi
If you get the below things
No row was updated.
The data in row X 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(N rows).
Correct the errors and retry or press ESC to cancel the change(s).
If you receive this message dialog box, you cannot update the row.
This issue occurs if the following conditions are true:
The table contains one or more columns of the text or ntext data type.
The value of one of these columns contains the following characters:
Percent sign (%)
Underscore (_)
Left bracket ([)
The table does not contain a primary key.
Note This issue also occurs when you try to use Table Designer in Microsoft Visual Studio 2005 to update a table that is in a SQL Server 2005 database.
Thanks
Parthi
Thanks
Parthi
November 22, 2010 at 2:00 pm
I am using MS SQL Server Mgt Studio. I r-click on the table and select Open Table. I have a date field which I try and modify (I have tried other fields as well). Looks like the only condition that is true is that there is no key on the table (Tabke A or B).
Do I need the key on both?
November 22, 2010 at 2:04 pm
Rog Saber (11/22/2010)
I am using MS SQL Server Mgt Studio. I r-click on the table and select Open Table. I have a date field which I try and modify (I have tried other fields as well). Looks like the only condition that is true is that there is no key on the table (Tabke A or B).Do I need the key on both?
Table A. SSMS can't modify a specific row in the table via the open table structure without a PK to confirm, definitively, that there is only one row it's updating.
Thanks Parthi, I'd forgotten the error coughs up for that too.
Rog, as a side note, you really want to get out of the habit of ever using the Open Table mechanic. It does all sorts of annoying things to the database with locks and the like.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 2:27 pm
I added indexes to the tables same problem. I don't understand because none of my other tables have PK, just a lot of indexes built from not null columns.
November 22, 2010 at 2:34 pm
Rog Saber (11/22/2010)
I added indexes to the tables same problem. I don't understand because none of my other tables have PK, just a lot of indexes built from not null columns.
You don't need an index, you need a Primary Key Constraint.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 22, 2010 at 2:49 pm
I added PK to Table A and B. The update worked on A and the insert worked on B - thank you for your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply