代码之家 › 专栏 › 技术社区 › Michael L

非常奇怪的SQL更新问题

sql-update sql

Michael L · 技术社区 · 16 年前

我还有一个叫做ApprovalView的视图

当我尝试直接更新时:

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_批准_任务(群集)

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_批准任务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

4 回复 | 直到 12 年前

VVS 16 年前

幸运猜测:是否定义了更新触发器?

第二个幸运猜测:ApprovalView是一个 indexed view 更新ApprovalTask表后违反了其索引。

Learning 16 年前

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

Nick Kavadias 16 年前

ApprovalTaskID、StatusID上是否可能存在唯一的键约束,这意味着表中还有一行状态为2&ApprovalTaskID=48

DISABLE TRIGGER ALL ON ApprovalTasks 然后重新运行更新

elpablo 16 年前

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

可能视图包含来自不同表的不兼容数据,或者设置了更严格的约束?