[SQL] 可更新的 View

View 是虛擬的 Table，除了可以用來查詢，簡化 T-SQL 語法外，還可以透過 View 對 Tabel 進行 DML 操作

MSDN 可更新 View 條件說明

1. 包括 UPDATE、INSERT 和 DELETE 陳述式在內的任何修改都只能參考一份基底資料表的資料行。
2. 檢視所修改的資料行必須直接參考資料表資料行中的基礎資料。 您無法利用任何其他方法來衍生這些資料行，例如：
   
   • 彙總函式：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。
   • 計算。 您不能從使用其他資料行的運算式計算資料行。 利用設定運算子 UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT 形成的資料行會得出一項計算，這些資料行無法更新。
3. GROUP BY、HAVING 或 DISTINCT 子句不會影響所修改的資料行。
4. 檢視的 select_statement 中任何位置都不會使用 TOP 與 WITH CHECK OPTION 子句。

Sample Data

USE [AdventureWorks2012]
GO
 
IF OBJECT_ID('Orders') IS NOT NULL
  DROP TABLE Orders
 
IF OBJECT_ID('OrdersDetail') IS NOT NULL
  DROP TABLE OrdersDetail
 
IF OBJECT_ID('vwDemo') IS NOT NULL
  DROP VIEW vwDemo
 
CREATE TABLE Orders 
    (
        OrderNO char(10) Primary Key,
        OrderDate date,
        Total money
    )

CREATE TABLE OrdersDetail 
    (
        ID int identity Primary Key,
        OrderNO char(10),
        ProdID int,
        ProductName nchar(20),
        UnitPrice money,
        Qty int
    )
 
INSERT INTO Orders VALUES
  ('1030206001','20140206',1889)
INSERT INTO OrdersDetail VALUES
  ('1030206001',1,N'SQL Server 2012 管理',456,1),
  ('1030206001',2,N'SQL Server 2012 設計',400,2),
  ('1030206001',3,N'SQL Server 2012 效能調校',633,1)
GO 
 
CREATE VIEW vwDemo
AS
  SELECT
    O.OrderNO,
    O.OrderDate,
    O.Total,
    OD.ProdID,
    OD.ProductName,
    OD.UnitPrice,
    OD.Qty,
    UnitPrice * Qty AS SubTotal
  FROM Orders AS O
    JOIN OrdersDetail AS OD ON O.OrderNO = OD.OrderNO 
  WHERE O.OrderDate >= '20140101'
GO

範例 1

DELETE FROM vwDemo WHERE OrderNO = '1030206001'

檢視或函數 'vwDemo' 不是可更新的，因為修改會影響多個基底資料表。

範例 2

UPDATE vwDemo1 SET SubTotal = 100 WHERE OrderNO = '1030206001' AND ProdID = 1

由於內含衍生或常數欄位，導致檢視或函數 'vwDemo' 的更新或插入失敗。

範例 3

MSDN CHECK OPTION

強制執行所有針對檢視來執行的資料修改陳述式遵照 select_statement 內所設定的準則。 當利用檢視來修改資料列時，WITH CHECK OPTION 可確保在認可修改之後，仍可以透過檢視見到資料。

對 View 進行 insert Data

INSERT INTO vwDemo (OrderNO,OrderDate) VALUES('1021231001','20131231')
INSERT INTO vwDemo (OrderNO,OrderDate) VALUES('1030210001','20140210')

從下圖可以了解，View 在沒有 Check 條件限制下，即使該資料並不符合 WHERE 條件，一樣能透過 View 把 Data Insert 進入 Table

修改 View，加上 Check 條件限制

ALTER VIEW vwDemo
AS
  SELECT
    O.OrderNO,
    O.OrderDate,
    O.Total,
    OD.ProdID,
    OD.ProductName,
    OD.UnitPrice,
    OD.Qty,
    UnitPrice * Qty AS SubTotal
  FROM Orders AS O
    JOIN OrdersDetail AS OD ON O.OrderNO = OD.OrderNO 
  WHERE O.OrderDate >= '20140101'
  WITH CHECK OPTION     -- 新增 check 限制
GO
 
-- Insert data 來測試
INSERT INTO vwDemo (OrderNO,OrderDate,Total) VALUES('1021231002','20131231',0)

嘗試插入或更新已經失敗，因為目標檢視指定了 WITH CHECK OPTION 或跨越指定了 WITH CHECK OPTION 的檢視，而該作業產生的一個或多個資料列在 CHECK OPTION 條件約束下並不合格。

參考資料
• 論壇問題出處
• 透過檢視修改資料
• CREATE VIEW