更新SQL视图 [英] Updating SQL views

问题描述

是否可以更新复杂的视图(具有许多表关系的视图)?我在asp中的查询正在尝试更新视图，并且出现以下错误:更新或刷新的键列信息不足"

谢谢

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

推荐答案

无法使用视图更新数据.视图根本不支持更新或删除操作符.

It is not possible to update data using a view. A view simply does not support update or delete operatoins.

使用代替触发器来解决此问题.

像这样的东西:

Use an instead of trigger to work around this problem.

Something like:

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

上面的代码用于插入记录，但与update
类似
问候
Espen Harlinn

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

Regards
Espen Harlinn

这篇关于更新SQL视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！