Postgresql 函数、触发器写法

lmwtzw6u5l0

　　1、数据库环境

-- Table: 学生分数表
CREATE TABLE stu_score
(
stuno serial NOT NULL,    --学生编号
major character varying(16),   --专业课程
score integer     --分数
)
WITH (
OIDS=FALSE
);
ALTER TABLE stu_score OWNER TO postgres;


-- Table: 专业状态表，存储哪个专业有多少学生报名
CREATE TABLE major_stats
(
major character varying(16),    --专业课程
total_score integer,      --总分
total_students integer   --学生总数
)
WITH (
OIDS=FALSE
);
ALTER TABLE major_stats OWNER TO postgres;
 　　2、函数、存储过程

create or replace function fun_stu_major()
returns trigger as
$BODY$
DECLARE
rec record;
BEGIN
DELETE FROM major_stats;--将统计表里面的旧数据清空
FOR rec IN (SELECT major,sum(score) as total_score,count(*) as total_students
FROM stu_score GROUP BY major) LOOP
INSERT INTO major_stats VALUES(rec.major,rec.total_score,rec.total_students);
END LOOP;
return NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
 　　3、正式创建触发器trigger

create trigger tri_stu_major
AFTER insert or update or delete
on stu_score
for each row
execute procedure fun_stu_major()