Very strange SQL update issue

Question

I have a table called ApprovalTasks... Approvals has a status column

I also have a view called ApprovalsView

When I try a straight update :

update ApprovalTasks set Status = 2 where ApprovalTaskID = 48

I'm getting this error message:

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.

Any idea why this is happening?

Here is the create table script:

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_ApprovalTaskID(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]

Create View Script

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

look at the definition of the index IX_ApprovalTaskID Is it possible there is a unique key constraint on ApprovalTaskID, StatusID which would mean there is another row in the table with Status = 2 & ApprovalTaskID = 48

I agree with user Learning, it looks like there's a FOR UPDATE trigger on ApprovalTasks that is inserting an ApprovalTaskID into ApprovalView

Try running DISABLE TRIGGER ALL ON ApprovalTasks and re-run the update

Answer 2

Lucky guess: any update triggers defined?

2nd lucky guess: ApprovalsView is an indexed view whose index is violated after updating the ApprovalTask table.

Answer 3

It looks like a view has been created which is either of, or includes, the table you are updating and that the error occurs when the view is being updated with the changes.

Perhaps the view contains data from different tables which is incompatible, or has stricter constraints set?

Answer 4

Since the error is coming from the object dbo.ApprovalsView , the problem is with a trigger on ApprovalsTask which is trying to update that table. I really think that ApprovalsView is a table and not a view. But you must have already checked for that.

Answer 5

Come on, don't leave us all hanging: post the create view!