postgresql 多表可更新视图——触发器

//创建视图

create view v_user2 as
select s.sid,u.sid as uid,s.id,s.name,u.uname,u.password,u.power
from student s join  users u
on s.sid = u.sid;

CREATE OR REPLACE FUNCTION v_user2_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
insert into users(uname,password,power)values(new.uname,new.password,new.power);
insert into student(id,name) values(new.id,new.name);
RETURN NULL;
END;$$
LANGUAGE Plpgsql;

CREATE OR REPLACE FUNCTION v_user2_update_trigger()
RETURNS TRIGGER AS $$
BEGIN
update users set uname=new.uname,password=new.password,power=new.power where sid=new.sid;
update student set id=new.id,name=new.name where sid=new.sid;
RETURN NULL;
END; $$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION v_user2_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
DELETE FROM users WHERE sid= old.sid;
DELETE FROM student WHERE sid= old.sid;
RETURN NULL;
END; $$
LANGUAGE plpgsql;

CREATE TRIGGER insert_v_users_trigger
    INSTEAD OF INSERT ON v_user2
    FOR EACH ROW EXECUTE PROCEDURE v_user2_insert_trigger();
CREATE TRIGGER update_v_users_trigger
    INSTEAD OF UPDATE ON v_user2
    FOR EACH ROW EXECUTE PROCEDURE v_user2_update_trigger();
CREATE TRIGGER delete_v_users_trigger
    INSTEAD OF DELETE ON v_user2
    FOR EACH ROW EXECUTE PROCEDURE v_user2_delete_trigger();