ORA-01779問題的解決

昨天幫一個師弟看一個ORACLE觸發器T的問題，這個觸發器建在視圖V上，當使用者往視圖裡插記錄的時候就自動插到視圖的組成表裡。但是每次插入總提示，ORA-01779 cannot modify a column which maps to a non key-preserved table。而怪異的是，有一個視圖V2和V的構成方式一摸一樣，但是它的觸發器T2就沒問題。在網上找了很多文章，絕大部分原因都是插入的基本表記錄不唯一，我檢查了觸發器內容和基本表的keys，都沒問題。

到第二天的時候突然看到一個文章的原因解釋：

view的更改是有限制的

General Rule
Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

UPDATE Rule
All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are non-updatable.

DELETE Rule
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted from the view.

INSERT Rule
An INSERT statement must not explicitly or implicitly refer to the columns of a non-key preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT statements are not permitted.

（http://www.itpub.net/225291.html）

再檢查了觸發器，發現事件那塊只關聯了UPDATE，其他都沒關聯，粗心啊。全部關聯上就好了。

看來觸發器的幾個選項一定要好好檢查啊。