导致主键冲突的触发器

Question

我们有一个触发器，我们正在尝试更新该触发器，以便在更新记录时，触发器将更新第二个表。当我们激活更改时，我们收到一个错误

违反主键约束"KPRIMARY_SO_SalesOrderHeader"。无法在对象 'dbo 中插入重复键。SO_SalesOrderHeader"。重复的密钥是 XXXXXX。

我对此感到非常困惑，因为我在触发器中看不到我们尝试插入键的位置。

ALTER Trigger [dbo].[SO_SALESORDERHEADER_onOrderChange] 
ON [dbo].[SO_SalesOrderHeader] 
FOR INSERT, UPDATE
AS 
SET NOCOUNT ON;
--+
INSERT INTO dbo.OrderUpdateQueue (SourceId, OrderNumber, Action)
SELECT DISTINCT
N'SO', Inserted.SALESORDERNO, N'U'
FROM
Inserted
LEFT JOIN
dbo.OrderUpdateQueue ON (Inserted.SALESORDERNO = OrderUpdateQueue.OrderNumber)
AND (OrderUpdateQueue.SourceID = N'SO')
AND (OrderUpdateQueue.Action = N'U')
WHERE 
(OrderUpdateQueue.[Key] IS NULL)
AND (Inserted.SALESORDERNO IS NOT NULL);
--+ New Trigger Stuff
UPDATE MO
SET MO.[ShipDate] = I.ShipExpireDate
FROM [BACKEND_db].[dbo].[Order] MO
INNER JOIN Inserted I ON I.SalesOrderNo = MO.OrderId
WHERE MO.ShipDate <> I.ShipExpireDate;

CREATE TABLE [dbo].[SO_SalesOrderHeader](
[SalesOrderNo] [varchar](7) NOT NULL
CONSTRAINT [KPRIMARY_SO_SalesOrderHeader] PRIMARY KEY CLUSTERED 
(
[SalesOrderNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[OrderUpdateQueue](
[Key] [int] IDENTITY(1,1) NOT NULL,
[SourceId] [nvarchar](50) NOT NULL,
[OrderNumber] [nvarchar](50) NOT NULL,
[Action] [nchar](1) NOT NULL,
CONSTRAINT [PK_OrderUpdateQueue] PRIMARY KEY CLUSTERED 
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [BACKEND_db]
CREATE TABLE [dbo].[Order](
[Key] [int] IDENTITY(1,1) NOT NULL,
[OrderId] [nvarchar](10) NULL,
[ShipDate] [datetime] NULL
CONSTRAINT [PK_MasOrder] PRIMARY KEY CLUSTERED 
(
[Key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

Answer 1

你如何不发布 OrderUpdateQueue 表的架构，我相信 pk 是订单号字段。您可以尝试将触发器更改为：

ALTER Trigger [dbo].[SO_SALESORDERHEADER_onOrderChange] 
ON [dbo].[SO_SalesOrderHeader] 
FOR INSERT, UPDATE
AS 
SET NOCOUNT ON;
--+
INSERT INTO dbo.OrderUpdateQueue (SourceId, OrderNumber, Action)
SELECT DISTINCT
N'SO', Inserted.SALESORDERNO, N'U'
FROM
Inserted 
WHERE not exists (SELECT 1 FROM dbo.OrderUpdateQueue  where Inserted.SALESORDERNO = OrderUpdateQueue.OrderNumber) 
UPDATE MO
SET MO.[ShipDate] = I.ShipExpireDate
FROM [BACKEND_db].[dbo].[Order] MO
INNER JOIN Inserted I ON I.SalesOrderNo = MO.OrderId
WHERE MO.ShipDate <> I.ShipExpireDate;