Skip to content

Instantly share code, notes, and snippets.

@abarth500 abarth500/gist:7762705
Last active Dec 30, 2015

Embed
What would you like to do?
[データベースシステム論] 第9回 更新可能トリガ
--ビュー定義
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
);
--成功した?
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.