创建触发器

1)先为触发器建一个执行函数,此函数的返回类型为触发器类型

2)建一个触发器

postgres=# create 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
postgres=#
postgres=# create trigger delete_student_trigger
postgres-# after delete on student
postgres-# for each row execute procedure student_delete_trigger();
CREATE TRIGGER
postgres=# insert into student values(1,'张三',14);
insert into student values(2,'李四',14);
INSERT 0 1
postgres=# insert into student values(2,'李四',14);
INSERT 0 1
postgres=# insert into student values(3,'王二',14);
INSERT 0 1
postgres=#
postgres=# insert into score values(1,85,75,date '2022-06-29');
INSERT 0 1
postgres=# insert into score values(1,80,73,date '2022-01-29');
INSERT 0 1
postgres=# insert into score values(2,87,75,date '2022-03-29');
INSERT 0 1
postgres=# insert into score values(3,75,75,date '2022-06-29');
INSERT 0 1
postgres=# insert into score values(3,55,75,date '2022-04-29');
INSERT 0 1
postgres=# delete from student where student_no=3;
DELETE 1
postgres=# select * from score;
student_no | chinese_score | math_score | test_date
------------+---------------+------------+------------
1 | 85 | 75 | 2022-06-29
1 | 80 | 73 | 2022-01-29
2 | 87 | 75 | 2022-03-29
(3 rows)

语句级触发器

语句级触发器指执行每个sql语句时只执行一次。

create table log_student(
update_time timestamp,
db_user varchar(40),
opr_type varchar(6)
)

create or replace function log_student_trigger()
returns trigger as $$
begin
insert into log_student values (now(),user,TG_OP);
return null;
end;
$$
language "plpgsql";

TG_OP是触发器函数中的特殊变量,代表DML操作类型

create trigger log_student_trigger
after insert or delete or update on student
for statement execute procedure log_student_trigger();

虽然删了2条记录,但是执行的是一条语句,所以在log_student中只记录了一次操作。

postgres=# delete from student;
DELETE 2
^
postgres=# select * from log_student;
update_time | db_user | opr_type
----------------------------+----------+----------
2022-06-30 10:26:48.847478 | postgres | DELETE
(1 row)

行级触发器

行级触发器执行每行SQL语句都会执行一次

drop trigger log_student_trigger on student;
delete from log_student;
delete from student;
create trigger log_student_trigger2 
after insert or delete or update on student
for ROW EXECUTE PROCEDURE log_student_trigger();



postgres=# insert into student values (1,'张三',14),(2,'李四',30);
INSERT 0 2
postgres=# select * from log_student;
update_time | db_user | opr_type
----------------------------+----------+----------
2022-06-30 10:42:58.834223 | postgres | INSERT
2022-06-30 10:42:58.834223 | postgres | INSERT
(2 rows)

可以看到一行sql执行插入2行数据,日志表中记录了2条记录


BEFORE触发器

语句级别的BEFOR触发器是在语句开始做任何事情之前就被触发的

行级别的BEFORE触发器是在对特定行进行操作之前触发的。


AFTER触发器

语句级的AFTER触发器是在语句结束时才触发的

行级别的AFTER触发器是在语句结束时才触发的,它会在任何语句级别的AFTER触发器之前触发。


删除触发器

drop trigger [if exists] name ON table [cascade |restrict];

if exists 如果触发器不存在,发出一个notice而不是一个错误

cascade级联删除依赖此触发器的对象

restrict有依赖的对象就拒绝删除

注意:删除触发器时,触发器的函数不会被删除,不过,删除表时,表上的触发器会被删除。


触发器函数有返回值,语句级触发器应该总是返回NULL,即必选显示的在触发器函数写上return null,否则报错。