Oracle PL/SQL – INSTEAD OFトリガーの例

CREATE TABLE customer__details
(
    customer__id number(10) primary key,
    customer__name varchar2(20),
    country varchar2(20)
);

CREATE TABLE projects__details
(
    project__id number(10) primary key,
    project__name varchar2(30),
    project__start__Date date,
    customer__id number(10) references customer__details(customer__id)
);

CREATE OR REPLACE VIEW customer__projects__view AS
   SELECT cust.customer__id, cust.customer__name, cust.country,
          projectdtls.project__id, projectdtls.project__name,
          projectdtls.project__start__Date
   FROM customer__details cust, projects__details projectdtls
   WHERE cust.customer__id = projectdtls.customer__id;

INSERT INTO customer__projects__view VALUES (1,'XYZ Enterprise','Japan',101,'Library management',sysdate);

-- output

ORA-01779:非キー保存表にマップする列を変更できません01779。00000  -  "非キー保存表にマップする列を変更できません" ** 原因:挿入または更新しようとしました非キー保存テーブルにマップされる結合ビューの列。

** 処置:基礎となる基本表を直接変更してください。

TRIGGERの作成または置換trg__cust__proj__view__insert
   INSERT ONの代わりにcustomer__projects__view
   宣言
     duplicate__info EXCEPTION;
     PRAGMA EXCEPTION__INIT（duplicate__info、-00001）;
   ベギン
     
   INSERT INTO customer__details
       （customer__id、customer__name、country）
     VALUES（:new.customer__id、:new.customer__name、:new.country）;
     
   INSERT INTO projects__details（project__id、project__name、project__start__Date、customer__id）
   VALUES（
     :new.project__id、
     :new.project__name、
     :new.project__start__Date、
     :new.customer__id）;
  
   例外
     WHEN duplicate__info THEN
       RAISE__APPLICATION__ERROR（
         num => -20107、
         msg => 'Duplicate customer or project id'）;
   END trg__cust__proj__view__insert;

INSERT INTO customer__projects__view VALUES（1、 'XYZエンタープライズ'、 '日本'、101、 'ライブラリ管理'、sysdate）;

INSERT INTO customer__projects__view VALUES（2、 'ABCインフォテック'、 'インド'、202、 'HR管理'、sysdate）;

SELECT **  FROM customer__details;

SELECT **  FROM projects__details;

SELECT **  FROM customer__projects__view;

テーブルを作成するvehicle__mfg__company__details（
  company__id番号（10）主キー、
  company__name varchar2（50）NOT NULL
）;

CREATE TABLEのvehicle__details（
  vehicle__id番号（10）主キー、
  company__id番号（10）は、vehicle__mfg__company__details（company__id）、
  vehicle__model__name varchar2（50）NOT NULL
）;

CREATE OR REPLACE TYPE nestedTableEle
IS
OBJECT（
  vehicle__id NUMBER（10）、
  vehicle__model__name VARCHAR2（50）
  ）;

-- nested table view column
CREATE OR REPLACE TYPE vehicle__details__list__ IS
  TABLE OF nestedTableEle;

CREATE OR REPLACE VIEW company__vehicles__view AS
  SELECT company.company__id,
         company.company__name,
         CAST (MULTISET (SELECT vehicle.vehicle__id, vehicle.vehicle__model__name
                         FROM vehicle__details vehicle
                         WHERE vehicle.company__id = company.company__id
                        )
                        AS vehicle__details__list__
              ) vehiclelist
FROM vehicle__mfg__company__details company;

-- no error.

INSERT INTO vehicle__mfg__company__details VALUES（101、 'Ford'）;

-- error
INSERT INTO TABLE (
  SELECT vw.vehiclelist
  FROM company__vehicles__view vw
  WHERE company__id = 101
)
VALUES (1, 'EcoSport');

-- output

SQLエラー:ORA-25015:このネストした表ビューの列25015でDMLを実行できません。00000  -  "このネストした表のビュー列でDMLを実行できません" ** 原因:INSTEAD OFトリガー以外のネストした表のビュー列でDMLを実行できません** 処置:ネストした表のビュー列に対してINSTEAD OFトリガーを作成し、DMLを実行してください。

トリガーの作成または置換trg__comp__vehicles__view__insrt入れ子になったテーブルの代わりに車両リストVehicleList of company__vehicles__view各車両の車両親としての新しい参照を開始する - ネストしたテーブルに挿入すると、ベーステーブルに挿入されます。

INSERT INTO vehicle__details（
    vehicle__id、
    company__id、
    vehicle__model__name
  ）
  VALUES（
    :Vehicle.vehicle__id、
    :Company.company__id、
    :Vehicle.vehicle__model__name
  ）;
終わり;

INSERT INTO TABLE（SELECT vw.vehiclelist FROM company__vehicles__view vw where company__id = 101）VALUES（1、 'EcoSport'）;

INSERT INTO TABLE（
  SELECT vw.vehiclelist
  FROM company__vehicles__view vw
  どこcompany__id = 101
）
VALUES（2、 'エンデバー'）;

車の中から選択してください.com__company__details;

選択**  from vehicle__details;