什么是触发器

触发器是与表事件相关的特殊的存储过程，它不由程序调动，不由手动调用，而是由事件触发。触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。

触发器的作用

会在指定的数据库事件发生时自动执行/调用，比如当INSERT、UPDATE或DELETE时，会按照设计的流程同步去插入、更新、删除其他表。

创建触发器

触发器创建步骤：

创建函数，函数返回类型为触发器
创建触发器示例：

--创建一张货物表，一张订单表
postgres=# create table goods(name text,price numeric(5,1));
CREATE TABLE
postgres=# create table orders(name text);
CREATE TABLE

--创建一个函数
create or replace function goods_delete_trigger()
returns trigger as
$$
begin
delete from goods where name like OLD.name;
return OLD;
end;
$$
language plpgsql;

--创建一个触发器
create trigger delete_trigger 
after delete on orders
for each row execute procedure goods_delete_trigger();

--测试触发器，往goods表及orders表插入数据
insert into goods values('pen',3.5);
insert into goods values('apple',12。1);
insert into orders values('apple');

postgres=# select * from goods ;
 name  | price
-------+-------
 apple |  12.1
 pen   |   3.5
(2 rows)

postgres=# select * from orders ;
 name
-------
 apple
(1 row)

--调用触发器
postgres=# delete from orders where name like 'apple';
DELETE 1
postgres=# select * from goods ;
 name | price
------+-------
 pen  |   3.5
(1 row)

postgres=# select * from orders ;
 name
------
(0 rows)

触发器分类

语句级触发器：指执行每个SQL语句时只执行一次。修改0行的操作会导致合适的语句级触发器被执行，即语句触发器即使没有更新数据，也会被触发

创建方法

同上例，只是“for each row”变为“for statement”

什么是“执行每个SQL语句只执行一次”

使用一个SQL语句时，可能插入或者更新、删除了多行，但仅触发一次触发器
行级触发器：执行每行SQL语句都会执行一次

创建方法

使用"for each row"

什么是“执行每行SQL语句都会执行一次”

如上例，使用一个SQL语句时，插入或者更新、删除了多行，那多就会触发多少次触发器，若更改了0行，则触发器不会被触发
BEFORE触发器及AFTER触发器

语句级的BEFORE触发器在语句开始前被触发，语句级的AFTER触发器在语句结束触发；行级BEFORE触发器在对特定行进行操作之前触发，行级别的AFTER触发器在语句结束时才触发，但会在任何语句级别的AFTER触发器之前被触发。

触发器的行为

触发器函数需要有返回值，如果是语句级触发器那么返回NULL，在触发器函数中需要显式写上"RETURN NULL"。如果没写，调用触发器时将报错。对于BEFORE和INSTEAD OF这类行级触发器来说，如果返回的是NULL，表示忽略当前行的操作。如果返回非NULL的行，对于INSERT和UPDATE操作来说，返回的行将成为被插入的行或者将要更新的行，对于AFTER这类行级触发器来说，返回值会被忽略。

触发器函数中的变量

触发器函数调用时，系统会在顶层声明段中自动创建几个特殊变量。如：

NEW：数据类型是record。在insert、update操作触发的行级触发器中存储新的数据行。
语句级别的触发器以及DELETE操作触发的行级触发器中此变量未分配。

OLD：数据类型是record。在update、delete操作触发的行级触发器中存储旧的数据行。
语句级别的触发器以及INSERT操作触发的行级触发器中此变量未分配

TG_NAME：数据类型是name。该变量为触发器名称。

TG_WHEN：内容为"BEFORE"或“AFTER”，用来判断是BEFORE触发器还是AFTER触发器。

TG_LEVEL：内容为“ROW”或“STATEMENT”，用来判断是语句级触发器还是行级触发器。

TG_OP：内容为“INSERT”、“UPDATE”、“DELETE”、“TRUNCATE”，用于指定DML语句类型。

TG_RELID：触发器所在表的 oid。

TG_TABLE_NAME：触发器所在表的表名称。

TG_SCHEMA_NAME：触发器所在表的模式。

TG_NARGS：在创建触发器语句中赋予触发器过程的参数个数。

TG_ARGV[]：text类型的数组。创建触发器语句中的参数

事件触发器

从PG9.3之后支持的事件触发器弥补了之前版本不支持DDL触发器的不足。事件触发器支持的3中DDL事件为：

ddl_command_start:DDL开始执行前被触发
ddl_command_end:DDL执行完成后被触发
sql_drop：删除数据库对象前被触发

注：事件触发器权限较大，仅超级用户可以进行创建及修改。

事件触发器创建、查看及修改

Command:     CREATE EVENT TRIGGER
Description: define a new event trigger
Syntax:
CREATE EVENT TRIGGER name
    ON event
    [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
    EXECUTE { FUNCTION | PROCEDURE } function_name()

事件触发器的函数返回类型为event_trigger。下例为禁用所有DDL语句的示例：

create or replace function abort_any_command()
returns event_trigger
language plpgsql
as
$$
begin
raise exception 'command % is disable', TG_TAG;
end;
$$;

--TG_TAG :指具体的DDL操作

create event trigger abort_ddl on ddl_command_start
execute procedure abort_any_command();

--执行DDL语句
postgres=# create table t1(a int);
ERROR:  command CREATE TABLE is disable
CONTEXT:  PL/pgSQL function abort_any_command() line 3 at RAISE

--truncate table 是可以执行的，事件触发器不会触发truncate table
postgres=# truncate t1;
TRUNCATE TABLE

禁止事件触发器

postgres=# alter event trigger abort_ddl disable;
ALTER EVENT TRIGGER

postgres=# create table t_1(a int);
CREATE TABLE

查看已有事件触发器

postgres=# select * from pg_event_trigger;
  oid  |  evtname  |     evtevent      | evtowner | evtfoid | evtenabled | evttags
-------+-----------+-------------------+----------+---------+------------+---------
 16626 | abort_ddl | ddl_command_start |       10 |   16625 | D          |
(1 row)

修改事件触发器

Command:     ALTER EVENT TRIGGER
Description: change the definition of an event trigger
Syntax:
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
ALTER EVENT TRIGGER name RENAME TO new_name

删除触发器

Command:     DROP TRIGGER
Description: remove a trigger
Syntax:
DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]

--if exists:如果指定的触发器不存在，那么给出一个notice而不是error
--cascade：级联删除依赖此触发器的对象
--restrict：默认值，有依赖对象存在就拒绝删除

参考《PostgreSQL修炼之道从小工到专家第2版》

PostgreSQL逻辑结构管理：触发器