Solved

SQL Server Management Studio Problem

Posted on 2013-05-21
12
672 Views
Last Modified: 2013-05-23
I have a problem with SQL Server Management studio 2005 and later versions.
I get an error if I manually change the value of a column in a record of a specific table.
The table in question has a trigger which affects more than one row in a different table and it would appear that SQL Server Management Studio cannot cope with this ?
If I change the table data with an update query then no error is generated and the records in the other table are changed by the trigger with no problem.
The changes in the other table do not affect each recor'd uniqueness so I guess that the problem is due to the changing of more than one record ?
The trigger is invoked after UPDATE,INSERT

The error message that I get is as follows:-
Error Source : Microsoft VisualStudio.Data tools
Erro Message:
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(5 rows)

Is there a fix for this ?
0
Comment
Question by:alcindor
12 Comments
 
LVL 20

Assisted Solution

by:dsacker
dsacker earned 250 total points
Comment Utility
If you are wanting to update this table outside of the design of the trigger, you can disable it first, then enable it afterwards. Otherwise, you'll need to revisit the trigger design. Assuming the former, you can do the following:

alter table YourTable disable trigger all
go

update YourTable
........
go

alter table YourTable enable trigger all
go
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
Comment Utility
Hi Alcindor,

This error usually happens when the record you are changing in your current table is not a unique record. This in turn can happen if your table doesn't have a primary key.
You can recreate this by creating a dummy table without a primary key and inserting the same values twice, then go to the management studio and try to edit one of the lines inserted. Because both lines are the same, SQL throws an error because it cannot handle the change.
If I'm not wrong the only fix is to delete both duplicate records at the same time, or if that's not allowed truncate the table.
If any other experts have another solution...

Rgds,

Kvwielink
0
 
LVL 12

Expert Comment

by:funwithdotnet
Comment Utility
Adding to Kvwielink's excellent response ...

It means that even though you are trying to alter one record, several records will be changed.

In my experience, that error often exposes data anomalies and slow performance. You may have two records that have a common value they should not have.

It might be a good idea to create a primary key, if missing.

TIP: For SQL Server 2008R2 > , a primary key int auto increment NOT NULL field can be added to an existing table in the designer and it'll populate. Very handy when updating old stuff.
0
 
LVL 2

Author Comment

by:alcindor
Comment Utility
I stated in my original question that the trigger will change more than one record in another table and that is what I wish the trigger to do.
The table being manually edited will only contain about 10 records and will seldom get altered. The intentin behind the trigger is to ensure integrity with a second table that has a common column, in this case the column name is "ioaddress".
When data in the first table is edited, the trigger is invoked and uses the value of the ioaddress column in the edited table to set values in the second table which have the same ioaddress value (in this case there are 5 such records that the trigger needs to change the values of. I have added a primary key to the second table but this makes no difference.
The values that are being changed in the first table are not the ioaddress values which are unique in the first table.

The actual trigger code is as follows, I have also moved the update code (commented out) into a stored procedure and invoked the stored procedure but the same problem arises:

CREATE TRIGGER [dbo].[Update2WSTagsF1]
   ON  [dbo].[ADAMIOConfig]
   AFTER  UPDATE, INSERT
AS
BEGIN
      declare @greenbit int
      declare @redbit int
      declare @plcaddress int

      set @greenbit      = (select GreenLampRelayNum from inserted)
      set @redbit            = (select RedLampRelayNum from inserted)
      set @plcaddress      = (select IOAddress from inserted)
      exec SP_Update2WSIOTagsF1 @plcaddress,@greenbit,@redbit
/**
      update [2WSIOTagsF1]
            set BitNum = @greenbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'greenbeacon')
      update [2WSIOTagsF1]
            set BitNum = @redbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'redbeacon')
**/
END
 
/*  Stored procedure code follows */
CREATE PROCEDURE [dbo].[SP_Update2WSIOTagsF1]
@plcaddress int,@greenbit int,@redbit int
AS
BEGIN
      update [2WSIOTagsF1]
            set BitNum = @greenbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'greenbeacon');
      update [2WSIOTagsF1]
            set BitNum = @redbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'redbeacon');

END
0
 
LVL 2

Author Comment

by:alcindor
Comment Utility
I fixed the problem by adding set NOCOUNT ON in the trigger code as follows:

CREATE TRIGGER [dbo].[Update2WSTagsF1]
   ON  [dbo].[ADAMIOConfig]
   AFTER  UPDATE, INSERT
AS
BEGIN
      declare @greenbit int
      declare @redbit int
      declare @plcaddress int

      set NOCOUNT ON;

      set @greenbit      = (select GreenLampRelayNum from inserted)
      set @redbit            = (select RedLampRelayNum from inserted)
      set @plcaddress      = (select IOAddress from inserted)

      update [2WSIOTagsF1]
            set BitNum = @greenbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'greenbeacon')
      update [2WSIOTagsF1]
            set BitNum = @redbit
            where (PLCAddress = @plcaddress) and (VisTagLabel = 'redbeacon')

END
0
 
LVL 2

Author Comment

by:alcindor
Comment Utility
I've requested that this question be closed as follows:

Accepted answer: 0 points for alcindor's comment #a39187090

for the following reason:

I would like to award dsacker for suggesting the need to revisit the trigger design.
I consider that my question asking for a fix of the problem was only answered in detail by my own comment.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
The table in question has a trigger which affects more than one row in a different table and it would appear that SQL Server Management Studio cannot cope with this ?
Did you really think that?

I fixed the problem by adding set NOCOUNT ON in the trigger code as follows:
Unfortunately you have not addressed the problem.  As everyone has already pointed out, the TRIGGER is not written correctly.  Whoever wrote it are not aware that TRIGGERs do not fire once per row.  So fix the TRIGGER code and you will not get any errors.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
Comment Utility
This is how the TRIGGER should have been written:
CREATE TRIGGER [dbo].[Update2WSTagsF1] ON [dbo].[ADAMIOConfig]
    AFTER UPDATE, INSERT
AS
    BEGIN
        SET NOCOUNT ON

        UPDATE  [2WSIOTagsF1]
        SET     BitNum = CASE VisTagLabel
                           WHEN 'greenbeacon' THEN i.GreenLampRelayNum
                           WHEN 'redbeacon' THEN i.RedLampRelayNum
                         END
        FROM    [2WSIOTagsF1] w
                INNER JOIN INSERTED i ON w.IOAddress = i.IOAddress
        WHERE   VisTagLabel IN ('greenbeacon', 'redbeacon')

    END

Open in new window

0
 
LVL 2

Author Closing Comment

by:alcindor
Comment Utility
To acperkins,
Using your code still yields an error if you don't include the SET NOCOUNT ON statement.
Adding the SET NOCOUNT ON statement fixes the problem.
Thank you for your suggested update code but the only thing that prevented the error messge was the SET NOCOUNT ON statement which I discovered myself.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
the only thing that prevented the error messge was the SET NOCOUNT ON statement which I discovered myself.
Do yourself a favor and read up on SET NOCOUNT ON, it has nothing whatsoever to do with the error you quoted in your original question.

At the simplest level, your error is reporting that there are duplicates it cannot resolve.  SET NOCOUNT ON or OFF merely hides or shows the count of the number of rows affected in any given SQL Statement.

I do realize your mind is made up and you are happy with the outcome, I just want to make sure any future readers don't come away with any illusions that using SET NOCOUNT ON is going to cause some miracle and the duplicates will go away.
0
 
LVL 2

Author Comment

by:alcindor
Comment Utility
If what you say is correct then I do not need the SET NOCOUNT ON statement and your suggested code for the trigger would run without error. It is the case however, that the error persists with your suggested code unless the SET NOCOUNT ON is used which if I understand you correctly is just hiding the fact that more than one row is affected.
The trigger is required to possibly change more than one row so are you saying that I shouldn't use the SET NOCOUNT ON statement even though you suggest it in your code for the trigger ?
Your earlier comment states " So fix the TRIGGER code and you will not get any errors. " and you include code which is how the trigger should have been written (including the SET NOCOUNT ON statement)
My original question states "The table in question has a trigger which affects more than one row in a different table " and therefore the trigger is requred to affect more than one row and in any event I will need to use the SET NOCOUNT ON statement if I use SQL Server Management Studion to edit the data in the [2WSIOTagsF1] table.
The remainder of your code for the trigger is a more efficient solution but does not get away from the fact that more than one row is affected and that alone did not fix the error message. If you can show me some trigger code which affects more than one row and does not need to use the SET NOCOUNT ON statement when using SQL Server Management Studio to edit the data then I would be grateful.

I appreciate your help in any case.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I give up or as they say you can lead a horse to water...
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now