Click here to Skip to main content
15,792,403 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Is it possible to update a complex view (a view that has many table relationships)? My query in asp is trying to update a view and I get the following error : "Insuffucient Key Column Information for Updating or refreshing"

Thanks
Posted

It is not possible to update data using a view. A view simply does not support update or delete operatoins.
 
Share this answer
 
v2
Comments
mhwasim 18-May-11 13:31pm    
view can be used to update a record.....delete cannot be performed
Use an instead of trigger to work around this problem.

Something like:
SQL
CREATE TRIGGER [PostalAreaInsertTrigger]
ON [PostalAreaView]
INSTEAD OF INSERT AS
  BEGIN
    DECLARE @cur CURSOR
    SET @cur = CURSOR FOR
      SELECT
        [Oid],
        [RegisteredTime],
        [RegisteredBy],
        [SystemTypeId],
        [Region],
        [Name],
        [FromTime],
        [ThroughTime]
      FROM inserted
    OPEN @cur
    DECLARE @Oid uniqueidentifier
    DECLARE @RegisteredTime DateTime
    DECLARE @RegisteredBy uniqueidentifier
    DECLARE @ObjectType int
    DECLARE @Region uniqueidentifier
    DECLARE @Name nvarchar(255)
    DECLARE @FromTime DateTime
    DECLARE @ThroughTime DateTime
    FETCH NEXT FROM @cur INTO
        @Oid,
        @RegisteredTime,
        @RegisteredBy,
        @ObjectType,
        @Region,
        @Name,
        @FromTime,
        @ThroughTime
    WHILE(@@fetch_status <> -1)
      BEGIN
        EXEC [PostalAreaInsert] @Oid,@RegisteredTime,@RegisteredBy,@ObjectType,@Region,@Name,@FromTime,@ThroughTime
        FETCH NEXT FROM @cur INTO
            @Oid,
            @RegisteredTime,
            @RegisteredBy,
            @ObjectType,
            @Region,
            @Name,
            @FromTime,
            @ThroughTime
      END
    CLOSE @cur
    DEALLOCATE @cur
  END

GO


The code above is for inserting records, but it's similar for update

Regards
Espen Harlinn
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900