SQL Server Management Studio Problem

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

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.

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

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

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.

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.

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.

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.

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.

I give up or as they say you can lead a horse to water...