非常奇怪的SQL更新问题

Question

我有一个名为 ApprovalTask​​s 的表... Approvals 有一个状态列

我还有一个名为 ApprovalsView 的视图

当我尝试直接更新时：

update ApprovalTasks set Status = 2 where ApprovalTaskID = 48

我收到此错误消息：

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.ApprovalsView' with unique index 'IX_ApprovalTaskID'.
The statement has been terminated.

知道为什么会发生这种情况吗？

这是创建表脚本：

USE [CSPMOSSApplication]
GO
/****** Object:  Table [dbo].[ApprovalTasks]    Script Date: 12/11/2008 12:41:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ApprovalTasks](
    [ApprovalTaskID] [int] IDENTITY(1,1) NOT NULL,
    [ApproverID] [int] NOT NULL,
    [DueDate] [datetime] NULL,
    [Status] [smallint] NOT NULL,
    [ApprovedRejectedDate] [datetime] NULL,
    [Reason] [nvarchar](1024) COLLATE Finnish_Swedish_CI_AS NULL,
    [OrganizationID] [int] NOT NULL,
    [TicketID] [int] NOT NULL,
    [Link] [nchar](255) COLLATE Finnish_Swedish_CI_AS NULL,
    [GlobalApproverID] [int] NULL,
 CONSTRAINT [PK_Approval_Tasks] PRIMARY KEY CLUSTERED 
(
    [ApprovalTaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [CSPMOSSApplication]
GO
ALTER TABLE [dbo].[ApprovalTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_Approval_Tasks_ApprovalTaskStatuses] FOREIGN KEY([Status])
REFERENCES [dbo].[ApprovalTaskStatuses] ([ApprovalTaskStatusID])
GO
ALTER TABLE [dbo].[ApprovalTasks] CHECK CONSTRAINT [FK_Approval_Tasks_ApprovalTaskStatuses]
GO
ALTER TABLE [dbo].[ApprovalTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_Approval_Tasks_Organizations] FOREIGN KEY([OrganizationID])
REFERENCES [dbo].[Organizations] ([OrganizationID])
GO
ALTER TABLE [dbo].[ApprovalTasks] CHECK CONSTRAINT [FK_Approval_Tasks_Organizations]
GO
ALTER TABLE [dbo].[ApprovalTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_Approval_Tasks_Tickets] FOREIGN KEY([TicketID])
REFERENCES [dbo].[Tickets] ([TicketID])
GO
ALTER TABLE [dbo].[ApprovalTasks] CHECK CONSTRAINT [FK_Approval_Tasks_Tickets]
GO
ALTER TABLE [dbo].[ApprovalTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_Approval_Tasks_Users] FOREIGN KEY([ApproverID])
REFERENCES [dbo].[Users] ([UserID])
GO
ALTER TABLE [dbo].[ApprovalTasks] CHECK CONSTRAINT [FK_Approval_Tasks_Users]

PK_Approval_Tasks(Clustered)

USE [CSPMOSSApplication]
GO
/****** Object:  Index [PK_Approval_Tasks]    Script Date: 12/11/2008 12:45:50 ******/
ALTER TABLE [dbo].[ApprovalTasks] ADD  CONSTRAINT [PK_Approval_Tasks] PRIMARY KEY CLUSTERED 
(
    [ApprovalTaskID] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

IX_ApprovalTask​​ID(Clsutered)

SE [CSPMOSSApplication]
GO
SET ARITHABORT ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
/****** Object:  Index [IX_ApprovalTaskID]    Script Date: 12/11/2008 12:47:27 ******/
CREATE UNIQUE CLUSTERED INDEX [IX_ApprovalTaskID] ON [dbo].[ApprovalsView] 
(
    [ApprovalTaskID] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

创建视图脚本

USE [CSPMOSSApplication]
GO
-- =============================================
-- Script Template
-- =============================================

-- [ApprovalTasks]: add columns Link, GlobalApproverID
IF NOT EXISTS(SELECT 1 FROM sysobjects,syscolumns WHERE sysobjects.id = syscolumns.id 
AND sysobjects.name = 'ApprovalTasks' AND syscolumns.name = 'Link')
BEGIN
    ALTER TABLE ApprovalTasks ADD [Link] [nchar] (255) COLLATE Finnish_Swedish_CI_AS NULL
    PRINT 'Column ApprovalTasks.Link was added.'
END
IF NOT EXISTS(SELECT 1 FROM sysobjects,syscolumns WHERE sysobjects.id = syscolumns.id 
AND sysobjects.name = 'ApprovalTasks' AND syscolumns.name = 'GlobalApproverID')
BEGIN
    ALTER TABLE ApprovalTasks ADD [GlobalApproverID] [int] NULL
    PRINT 'Column ApprovalTasks.GlobalApproverID was added.'

    ALTER TABLE [dbo].[ApprovalTasks]  WITH NOCHECK ADD  CONSTRAINT [FK_Approval_Tasks_GlobalApproverID] FOREIGN KEY([GlobalApproverID])
    REFERENCES [dbo].[Users] ([UserID])

    ALTER TABLE [dbo].[ApprovalTasks] CHECK CONSTRAINT [FK_Approval_Tasks_GlobalApproverID]
END

-- [ApprovalsView]
IF EXISTS (SELECT * FROM sys.fulltext_indexes fti WHERE fti.object_id = OBJECT_ID(N'[dbo].[ApprovalsView]'))
BEGIN
    DROP FULLTEXT INDEX ON [dbo].[ApprovalsView]
    PRINT 'FULLTEXT INDEX on [ApprovalsView] was dropped.'
END
GO

IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ApprovalsView]') AND name = N'IX_ApprovalTaskID')
BEGIN
    DROP INDEX IX_ApprovalTaskID ON [dbo].[ApprovalsView] WITH ( ONLINE = OFF )
    PRINT 'INDEX IX_ApprovalTaskID was dropped.'
END
GO

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ApprovalsView]'))
DROP VIEW [dbo].[ApprovalsView]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[ApprovalsView]
WITH SCHEMABINDING 
AS
SELECT      at.ApprovalTaskID, 
            at.ApproverID, 
            at.DueDate, 
            at.Status,  
            ats.ApprovalTaskStatusTranslationKey AS StatusText, 
            at.ApprovedRejectedDate, 
            at.Reason, 
            at.OrganizationID,
            ord.Name AS OrderName, 
            ord.TotalPrice, 
            ord.SubmitDate, 
            ord.OrdererID,
            usr.FirstName AS OrdererFirstName, 
            usr.LastName AS OrdererLastName,
            ordi.Items_Name AS ItemName,
            ordi.Items_Description AS ItemDescription,
            ordi.OtherInformation AS ItemInformation,
            oir.RecipientFullName,
            CONVERT(nvarchar(250), oir.DeliveryAddress) As DeliveryAddress,
            ti.Description

FROM        dbo.ApprovalTasks at
    INNER JOIN 
        dbo.ApprovalTaskStatuses ats ON ats.ApprovalTaskStatusID = at.Status
    INNER JOIN
        dbo.Orders_Items_Recipients oir ON oir.TicketID = at.TicketID
    INNER JOIN
        dbo.Orders_Items ordi ON ordi.Orders_ItemsID = oir.Orders_ItemsID
    INNER JOIN
        dbo.Orders ord ON ordi.OrderID = ord.OrderID 
    INNER JOIN
        dbo.Users usr ON ord.OrdererID = usr.UserID
    INNER JOIN
        dbo.Tickets ti ON ti.TicketID = at.TicketID
GO

CREATE UNIQUE CLUSTERED INDEX [IX_ApprovalTaskID] ON [dbo].[ApprovalsView] 
(
    [ApprovalTaskID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
GO

CREATE FULLTEXT INDEX ON [dbo].[ApprovalsView](
[DeliveryAddress] LANGUAGE [Neutral], 
[ItemDescription] LANGUAGE [Neutral], 
[ItemInformation] LANGUAGE [Neutral], 
[ItemName] LANGUAGE [Neutral], 
[OrdererFirstName] LANGUAGE [Neutral], 
[OrdererLastName] LANGUAGE [Neutral], 
[OrderName] LANGUAGE [Neutral], 
[Reason] LANGUAGE [Neutral], 
[RecipientFullName] LANGUAGE [Neutral])
KEY INDEX [IX_ApprovalTaskID] ON [ApprovalSearchCatalog]
WITH CHANGE_TRACKING AUTO
GO

ALTER FULLTEXT CATALOG [ApprovalSearchCatalog] rebuild

PRINT 'Catalog [ApprovalSearchCatalog] task to rebuild fulltext index was sent.'


-- STORED PROCEDURES

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ReceiveApprovalTasksFromQueue]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[ReceiveApprovalTasksFromQueue]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:      Petr Klozik
-- Create date: 19.11.2008
-- Description: Gets approvals which DueDate is over ReferenceDate (now)
-- =============================================
CREATE Procedure [dbo].[ReceiveApprovalTasksFromQueue] 
    @Limit int
As
BEGIN

    SET NOCOUNT ON;

    If Not @Limit Is Null Set RowCount @Limit

    -- Status: WaitingForApproval = 1
    Select Tasks.ApprovalTaskID
    From ApprovalTasks Tasks
    Where Status = 1 And DueDate < GetDate()

END
' 
GO
GRANT EXECUTE ON [dbo].[ReceiveApprovalTasksFromQueue] TO [OMT_IntegrationRole]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UpdateApprovalTaskInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UpdateApprovalTaskInfo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:      Klozik Petr
-- Create date: 2008-11-25
-- Description: Updates Approval task info to DB
-- =============================================
CREATE PROCEDURE [dbo].[UpdateApprovalTaskInfo] 
    @ApprovalTaskID int, 
    @DueDate datetime,
    @ApprovalRejectDate datetime,
    @Reason nvarchar(1024),
    @Status int,
    @GlobalApproverID int
AS
BEGIN
    SET NOCOUNT ON;

    Update ApprovalTasks
        Set DueDate = @DueDate,
        ApprovedRejectedDate = @ApprovalRejectDate, 
        Reason = @Reason,
        Status = @Status,
        GlobalApproverID = @GlobalApproverID
        Where ApprovalTaskID = @ApprovalTaskID
END

' 
GO
GRANT EXECUTE ON [dbo].[UpdateApprovalTaskInfo] TO [OMT_IntegrationRole]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserById]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:      Klozik Petr
-- Create date: 2008-12-04
-- Description: Gets user row by the specified ID.
-- =============================================

CREATE PROCEDURE [dbo].[GetUserById]
(
    @UserID int
)

AS
BEGIN
    SELECT
        UserID,
        RTRIM(SID) [SID],
        RTRIM(OMTGUID) [OMTGUID],
        RTRIM(UserAccount) [UserAccount],
        RTRIM(Email) [Email],
        RTRIM(FirstName) [FirstName],
        RTRIM(LastName) [LastName],
        RTRIM(Country) [Country],
        RTRIM(City) [City],
        RTRIM(PostalNumber) [PostalNumber],
        RTRIM(StreetAddress) [StreetAddress],
        RTRIM(PhoneNumber) PhoneNumber,
        Modified, 
        Deleted,
        Uploaded,
        UploadCode, 
        UploadStatus, 
        RTRIM(Users.ADUserAccount) AS ADUserAccount
    FROM 
        [dbo].[Users]
    WHERE 
        UserID = @UserID
END
' 
GO
GRANT EXECUTE ON [dbo].[GetUserById] TO [OMT_IntegrationRole]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetApprovalTaskInfoById]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetApprovalTaskInfoById]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
EXEC dbo.sp_executesql @statement = N'


-- =============================================
-- Author:      Petr Klozik
-- Create date: 19.11.2008
-- Description: Gets approvals which DueDate is over ReferenceDate (now)
-- =============================================
CREATE Procedure [dbo].[GetApprovalTaskInfoById] 
    @ApprovalTaskID int
As
BEGIN

    SET NOCOUNT ON;

    Declare @OrganizationID int
    Declare @CurrentApproverID int
    Declare @NewApproverID int
    Declare @NewOrganizationID int

    Select @OrganizationID = OrganizationID, @CurrentApproverID = ApproverID 
        From ApprovalTasks 
        Where ApprovalTaskID = @ApprovalTaskID

    Set @NewApproverID = (
            Select Top 1 o.GlobalApproverID
            From Organizations o
                Inner Join OrganizationDescendants od On od.OrganizationID = o.OrganizationID
            Where od.DescendantID = @OrganizationID
                And Not(o.GlobalApproverID Is Null)
            Order By o.OrganizationLevel Desc
        )

    If Not(@NewApproverID Is Null) 
    Begin
        Set @NewOrganizationID = (
            Select OrganizationID 
                from Organizations 
                Where GlobalApproverID = @NewApproverID)
    End

    Select Tasks.*, Tickets.Description AS TicketDescription, 
        Tickets.RequestorID, Tickets.OrdererID,
        @NewApproverID AS OrgGlobalApproverID, 
        @NewOrganizationID AS OrgGlobalApproverOrganizationID
    From ApprovalTasks Tasks
    inner join Tickets Tickets on Tasks.TicketID = Tickets.TicketID
    Where ApprovalTaskID = @ApprovalTaskID

END
' 
GO
GRANT EXECUTE ON [dbo].[GetApprovalTaskInfoById] TO [OMT_IntegrationRole]
GO

Answer 1

幸运的猜测：定义了任何更新触发器吗？

第二个幸运的猜测：ApprovalsView 是一个 索引视图，其索引为更新 ApprovalTask​​ 表后发生违反。

Answer 2

查看索引IX_ApprovalTask​​ID的定义
ApprovalTask​​ID、StatusID 是否可能存在唯一键约束，这意味着表中还有另一行 Status = 2 & ApprovalTask​​ID = 48

我同意用户学习的观点，看起来 ApprovalTask​​s 上有一个 FOR UPDATE 触发器，正在将 ApprovalTask​​ID 插入 ApprovalView

尝试运行 DISABLE TRIGGER ALL ON ApprovalTask​​s 并重新运行更新

Answer 3

由于错误来自对象 dbo.ApprovalsView ，问题出在 ApprovalsTask 上的触发器试图更新该表上。 我真的认为 ApprovalsView 是一个表而不是一个视图。 但你一定已经检查过了。

Answer 4

看起来已经创建了一个视图，该视图属于您正在更新的表，或者包含您正在更新的表，并且在使用更改更新视图时会发生错误。

也许视图包含来自不兼容的不同表的数据，或者设置了更严格的约束？