3
PG基础篇--逻辑结构管理（触发器）
source link: https://blog.51cto.com/u_13874232/5433077
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
PG基础篇--逻辑结构管理（触发器）
原创创建触发器
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)
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();
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)
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;
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)
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，否则报错。
- 赞
- 收藏
- 评论
- 分享
- 举报
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK