[データベースシステム論] 第９回　更新可能トリガ

gistfile1.sql

--ビュー定義
CREATE VIEW wine_list AS
   SELECT wid, name, district, price
      FROM wine JOIN vineyard 
      ON wine.did = vineyard.did;

--ビューに対するSELECT
SELECT * FROM wine_list;


--ここからビューを更新可能にする作業

--関数定義
CREATE OR REPLACE FUNCTION function_insert_wine_list()
RETURNS TRIGGER
AS $function_insert_wine_list$
BEGIN
   INSERT INTO wine VALUES(NEW.wid,NEW.name,(SELECT did FROM vineyard WHERE district = NEW.district),NEW.price);
   RETURN NULL;
END;
$function_insert_wine_list$ LANGUAGE plpgsql;

--トリガ定義
DROP TRIGGER IF EXISTS trigger_insert_wine_list ON wine_list;
CREATE TRIGGER trigger_insert_wine_list
   INSTEAD OF INSERT
   ON wine_list 
   FOR EACH ROW
   EXECUTE PROCEDURE function_insert_wine_list();

--更新してみる
INSERT INTO wine_list 
VALUES(
   (select max(wid)+1 from wine),
   'コンチャ・イ・トロ',
   'チリ',
   980
);
--成功した？