Modify update statement includes primary key in tr

I'm working with a product that is creating update statements that include the primary key of the table.

Simplified:

  UPDATE T SET PK1='ID1', PK2='ID2'... WHERE PK='ID1' AND PK2 ='ID2'...

Running that statement will violate the primary key constraint.

If run the same update without the PK1='ID1' in the SET the update runs without issue.

How can I make this work? Instead of update trigger?

I do not have any control over the product that is creating these statements.

INFO

There is one other trigger on the table.

TRIGGER [dbo].[DeleteExisting] ON  [dbo].[T] 
INSTEAD OF INSERT
DELETE FROM T WHERE PK1 in (SELECT PK1 FROM INSERTED) AND PK2 IN (SELECT PK2 FROM INSERTED);
INSERT INTO T
SELECT * FROM INSERTED;

Error:

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_T'. Cannot insert duplicate key in object 'dbo.T'.
The statement has been terminated.

TABLE:

CREATE TABLE [dbo].[T](
    [PK1] [varchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [PK2] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [CLOSED] [bit] NULL,
    [TYPE] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TAG] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    ...
    [Date] [datetime] NULL CONSTRAINT [DF__Z_T_Date]  DEFAULT (getdate()),
    [UDF17] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED 
(
    [PK1] ASC,
    [PK2] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Actual UPDATE by the product:

 exec sp_executesql N'UPDATE "DB".."T" SET "PK1"=@P1,"WOTYPE"=@P2,"TAG"=@P3,"SITE"=@P4,"BLDG"=@P5,"FLOOR"=@P6,"ROOM"=@P7,"REQCODE"=@P8,"SHORTDESC"=@P9,"DATEOPENED"=@P10,"PRIORITY"=@P11,"STATUS"=@P12,"SHOP"=@P13,"TRADE"=@P14,"EMPCODE"=@P15,"FAULT1"=@P16,"FAULT2"=@P17,"FAULT3"=@P18,"CLOSEREM"=@P19,"MRNOTE1"=@P20,"RTACCT"=@P21,"SPACCT1"=@P22,"SPACCT2"=@P23,"WONUMTRUN"=@P24,"DATETRUNPM"=@P25,"REQTRUN"=@P26,"DEVICEID"=@P27,"LOC"=@P28,"LOCSEG4"=@P29,"LOCSEG5"=@P30,"LOG"=@P31,"FAULTCOMB"=@P32,"EQUIPLOC"=@P33,"RQSTR"=@P34,"CLOSEDTXT"=@P35,"CLSCXL"=@P36,"PK2"=@P37,"CLOSEDESC"=@P38,"REQUEST"=@P39,"UDF17cboText"=@P40 WHERE "WONUM"=@P41 AND "WOTYPE"=@P42 AND "TAG"=@P43 AND "SITE"=@P44 AND "BLDG"=@P45 AND "FLOOR"=@P46 AND "ROOM"=@P47 AND "REQCODE"=@P48 AND "SHORTDESC"=@P49 AND "DATEOPENED"=@P50 AND "PRIORITY"=@P51 AND "STATUS"=@P52 AND "SHOP"=@P53 AND "TRADE"=@P54 AND "EMPCODE" IS NULL AND "FAULT1"=@P55 AND "FAULT2"=@P56 AND "FAULT3"=@P57 AND "CLOSEREM"=@P58 AND "MRNOTE1"=@P59 AND "RTACCT"=@P60 AND "SPACCT1"=@P61 AND "SPACCT2"=@P62 AND "WONUMTRUN"=@P63 AND "DATETRUNPM"=@P64 AND "REQTRUN"=@P65 AND "DEVICEID" IS NULL AND "LOC"=@P66 AND "LOCSEG4" IS NULL AND "LOCSEG5" IS NULL AND "LOG"=@P67 AND "FAULTCOMB" IS NULL AND "EQUIPLOC"=@P68 AND "RQSTR"=@P69 AND "CLOSEDTXT"=@P70 AND "CLSCXL"=@P71 AND "PK2"=@P72 AND "CLOSEDESC" IS NULL AND "REQUEST"=@P73 AND "UDF17cboText"=@P74',N'@P1 varchar(6),@P2 varchar(2),@P3 varchar(9),@P4 varchar(3),@P5 varchar(3),@P6 varchar(1),@P7 varchar(1),@P8 varchar(6),@P9 varchar(8),@P10 datetime,@P11 varchar(1),@P12 varchar(5),@P13 varchar(1),@P14 varchar(5),@P15 varchar(1),@P16 varchar(1),@P17 varchar(1),@P18 varchar(1),@P19 varchar(1),@P20 varchar(12),@P21 varchar(1),@P22 varchar(1),@P23 varchar(1),@P24 varchar(5),@P25 varchar(5),@P26 varchar(1),@P27 varchar(1),@P28 varchar(20),@P29 varchar(1),@P30 varchar(1),@P31 varchar(2),@P32 varchar(1),@P33 varchar(9),@P34 varchar(1),@P35 varchar(2),@P36 varchar(20),@P37 varchar(20),@P38 varchar(1),@P39 varchar(700),@P40 varchar(1),@P41 varchar(6),@P42 varchar(2),@P43 varchar(9),@P44 varchar(3),@P45 varchar(3),@P46 varchar(1),@P47 varchar(1),@P48 varchar(6),@P49 varchar(8),@P50 datetime,@P51 varchar(1),@P52 varchar(5),@P53 varchar(1),@P54 varchar(5),@P55 varchar(1),@P56 varchar(1),@P57 varchar(1),@P58 varchar(1),@P59 varchar(12),@P60 varchar(1),@P61 varchar(1),@P62 varchar(1),@P63 varchar(5),@P64 varchar(5),@P65 varchar(1),@P66 varchar(20),@P67 varchar(2),@P68 varchar(9),@P69 varchar(1),@P70 varchar(2),@P71 varchar(20),@P72 varchar(20),@P73 varchar(3000),@P74 varchar(1)','017901','PM','PAE-AHU02','BAK','PAE','','','XAHU-S','AHU/stop','2011-01-31 00:00:00:000','','SCHLD','','HMECH',NULL,'','','','','Parkridge ES','','','','17901','01/31','S',NULL,'BAK-PAE             ',NULL,NULL,'A1',NULL,'PAE-AHU02','','  ','                    ','tilt',NULL,'___ 01.','','017901','PM','PAE-AHU02','BAK','PAE','','','XAHU-S','AHU/stop','2011-01-31 00:00:00:000','','SCHLD','','HMECH','','','','','Parkridge ES','','','','17901','01/31','S','BAK-PAE             ','A1','PAE-AHU02','','  ','                    ','tilt','___ 01.     ',''

As a test I did this with no error:

exec sp_executesql 
N'UPDATE "JCTEST".."T" SET "WONUM"=@P1,"StagedBy"=@P2 WHERE "WONUM"=@P3 AND "StagedBy"=@P4',
N'@P1 varchar (11),@P2 varchar(20),@P3 varchar(11),@P4 varchar(20)' ,'017747','tilt','017747','tilt'

That proves I'm not violating the primary key. Is there some logging in sql server I can turn on? What can I do to find out what the real error is?

回答1:

There must be some other stuff going on like a trigger on the table

this runs fine

CREATE TABLE Test(id INT PRIMARY KEY, bla CHAR(1))
GO
INSERT Test VALUES(1,'a')
INSERT Test VALUES(2,'b')


UPDATE Test SET id = 1 WHERE id = 1

Find out if it has a trigger

SELECT OBJECT_NAME(parent_id),* 
 FROM sys.triggers
 WHERE OBJECT_NAME(parent_id) = 'YourTable'

回答2:

looks like a trigger problem to me. Below is some code that is the process I use for troubleshooting and fixing a trigger.

Create table #inserted (add all fiedl from the current tbale here)
Create table #deleted (add all fields from the current table here)

Insert into #inserted 
values (add the new values you would expect to get from the update statment of a particular record)

Insert into #deleted 
values (add the original values you would expect to get from a particular record)
begin tran
--to see what records would be deleted 
Select *
WHERE   PK1 in (SELECT PK1 FROM #INSERTED) 
        AND PK2 IN (SELECT PK2 FROM #INSERTED);

--run the actual code
DELETE FROM T 
WHERE   PK1 in (SELECT PK1 FROM #INSERTED) 
        AND PK2 IN (SELECT PK2 FROM #INSERTED);
--to see what records are going to be inserted
 SELECT * FROM #INSERTED 
--to see if you have any records still in inserted that are in table T
Select * from #inserted
join T on #inserted.PK1 = T.PK1
--run the actual code
 INSERT INTO T 
 SELECT * FROM #INSERTED;

 rollback tran

This code will let you be able to see exactly what the trigger will do and find the problem piece of the code. I suspect it is not deleting the records intended before doing the insert.