我正在尝试在PostgreSQL 9.6中的一个VIEW
上添加触发器。
这是我的视图:
CREATE VIEW names AS
SELECT one.name AS name_one, two.name AS name_two, three.name AS name_three
FROM table_one one
LEFT JOIN table_two two ON one.id = two.id
LEFT JOIN table_three three ON two.id = three.id;
这是我的触发函数:
CREATE OR REPLACE FUNCTION notify_name_changed() RETURNS trigger AS $BODY$
BEGIN
PERFORM pg_notify('name_changed', row_to_json(NEW)::text);
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;
我的
CREATE TRIGGER
如下:CREATE TRIGGER notify_name_changed INSTEAD OF INSERT OR UPDATE OR DELETE ON "names"
FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
当基础表中的某些事件发生时,这不会触发任何更改。 然而,创建3个单独的触发器可以实现,但与视图有点无关:
CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "one"
FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "two"
FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
CREATE TRIGGER notify_name_changed AFTER INSERT OR UPDATE OR DELETE ON "three"
FOR EACH ROW EXECUTE PROCEDURE notify_name_changed();
是否可以直接在视图上添加触发器,在基础表发生任何更改时触发?
该文涉及IT技术。
instead of
触发器会在你插入到视图时触发,而不是在你插入到其中一个基本表时触发。 - user330315插入
操作,那么这是否会破坏视图与基本表之间的关系,并且从某种程度上删除了视图的概念? - Alfred Balle