触发器(tigger)是一种由事物自动触发执行的特殊存储过程,这些事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。
一、创建触发器
create [ constraint ] trigger name { before | after | instead of} { event [ or....] } on table_name [from referenced_table_name]
{not deferrable | [deferrable] {intially immediate | initially deferred }}
[for [each] {row | statement}]
[when (condition)]
execute procedure function_name (arguments)
1 实例:删除学生表student的一条记录是,同事删掉score的成绩记录也删除。
1.1 创建表
postgres=# create table student(
student_no int primary key,
sutdent_name varchar(40),
age int);
CREATE TABLE
postgres=# create table score(
postgres(# student_no int,
postgres(# chinese_score int,
postgres(# test_date date);
CREATE TABLE
postgres=#
1.2 创建执行函数
postgres=# create or replace function student_delete_trigger()
postgres-# returns trigger as $$
postgres$# begin
postgres$# delete from score where student_no = OLD.student_no;
postgres$# return OLD;
postgres$# end;
postgres$# $$
postgres-# language plpgsql;
CREATE FUNCTION
1.3 在创建触发器
postgres=# create trigger delete_student_trigger
postgres-# after delete on student
postgres-# for each row execute procedure student_delete_trigger();
CREATE TRIGGER
postgres=#
1.4 插入数据
postgres=# insert into score values(1,85,date '2018-05-16');
INSERT 0 1
postgres=# insert into score values(2,56,date '2018-05-16');
INSERT 0 1
postgres=# insert into score values(1,88,date '2018-05-16');
INSERT 0 1
postgres=# insert into score values(2,33,date '2018-05-16');
INSERT 0 1
postgres=# insert into score values(3,43,date '2018-05-16');
INSERT 0 1
postgres=# insert into score values(3,53,date '2018-05-16');
INSERT 0 1
1.5 删除学号为3的学生
postgres=# delete from student where student_no = 3;
DELETE 1
postgres=# select * from score;
student_no | chinese_score | test_date
------------+---------------+------------
1 | 85 | 2018-05-16
2 | 56 | 2018-05-16
1 | 88 | 2018-05-16
2 | 33 | 2018-05-16
(4 rows)
postgres=#
未完待续。。。