Very strange problem with SQL update

I have a table called Approval ... Approvals have a status column

I also have ApprovalsView

When I try direct update:

update ApprovalTasks set Status = 2 where ApprovalTaskID = 48

      

        
        
        
      

    

I am 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 script table:

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 Script View

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