PgSQL 触发器

触发器（Trigger）是由事件自动触发执行的一种特殊的存储过程，触发事件可以是对一个表进行INSERT、UPDATE、DELETE等操作。

创建触发器

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
  ON table_name
  [ FROM referenced_table_name ]
  { NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } }
  [ FOR [ EACH ] { ROW | STATEMENT } ]
  [ WHEN ( condition ) ]
  EXECUTE PROCEDURE function_name ( arguments )

创建触发器的步骤如下：

先为触发器建一个执行函数，此函数的返回类型为触发器类型。
然后建一个触发器。

假设有一张学生表（student表）和一张学生的考试成绩表（score表）

CREATE TABLE student(
  student_no int primary key, 
  student_name varchar(40), 
  age int
);
CREATE TABLE score (
  student_no int,
  chinese_score int,
  math_score int,
  test_date date
);

如果想要在删除学生表中的一条记录时同时删除该学生在成绩表（score表）中的成绩记录，就可以使用触发器。先建触发器的执行函数：

CREATE OR REPLACE FUNCTION student_delete_trigger()
RETURNS TRIGGER AS $$
BEGIN
  DELETE FROM score WHERE student_no = OLD.student_no;
  RETURN OLD;
END;
$$
LANGUAGE plpgsql;

再创建触发器：

CREATE TRIGGER delete_student_trigger
  AFTER DELETE  ON student
  FOR EACH ROW EXECUTE PROCEDURE student_delete_trigger ();

语句级触发器与行级触发器

语句级触发器是指执行每个SQL语句时只执行一次，行级触发器是执行每行SQL语句都会执行一次。一个修改0行的操作仍然会导致合适的语句级触发器被执行。下面来看看相应的示例。

假设要对表“student”的更新情况记录log，可以为student表建一张log表，命令如下：

CREATE TABLE log_student(
  update_time  timestamp, --操作的时间
  db_user varchar(40), --操作的数据库用户名
  opr_type varchar(6) --操作类型：insert、delete、update
);

创建记录log的触发器函数，命令如下：

CREATE 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操作类型。

然后在表“student”上创建一个语句级触发器：

CREATE TRIGGER log_student_trigger
  AFTER INSERT OR DELETE OR UPDATE  ON student
  FOR STATEMENT EXECUTE PROCEDURE log_student_trigger ();

语句触发器：不管影响了多少行也只会执行一次

然后建一个行级触发器：

CREATE TRIGGER log_student_trigger2
  AFTER INSERT OR DELETE OR UPDATE  ON student
  FOR ROW EXECUTE PROCEDURE log_student_trigger ();

行级触发器：对多少行数据有影响，就会执行多少次

BEFORE触发器与AFTER触发器

语句级

BEFORE触发器：是在语句开始做任何事情之前就被触发了的
AFTER触发器：是在语句结束时才触发的。
行级
BEFORE触发器：是在对特定行进行操作之前触发的
AFTER触发器：是在语句结束时才触发的，但是它会在任何语句级别的AFTER触发器之前触发。

BEFORE触发器可以直接修改NEW值以改变实际的更新值，具体示例如下。

CREATE FUNCTION student_use_new_name_tirgger ()
RETURNS trigger AS '
BEGIN
  NEW.student_name = NEW.student_name||NEW.student_no;
  RETURN NEW;
END;'
LANGUAGE "plpgsql";

上面这个函数的作用是，在插入或更新表时，把“student_name”后加上“student_no”，也就是直接修改“NEW.student_name”，语句如下：

NEW.student_name = NEW.student_name||NEW.student_no;

在这种情况下，只能使用BEFORE触发器

CREATE TRIGGER user_new_name_student_trigger
BEFORE INSERT OR UPDATE  ON student
FOR EACH ROW EXECUTE PROCEDURE student_use_new_name_tirgger ();

INSERT INTO student values(3,'王二', 15);


SELECT * FROM student;
 student_no | student_name | age 
------------+--------------+-----
          3 | 王二3        |  15
(1 rows)

删除触发器

DROP TRIGGER [ IF EXISTS ] name ON table [ CASCADE | RESTRICT ];

其中的语法说明如下。

IF EXISTS：如果指定的触发器不存在，那么发出一个notice而不是抛出一个错误。
CASCADE：级联删除依赖此触发器的对象。
RESTRICT：默认值，有依赖对象存在就拒绝删除。

DROP  TRIGGER user_new_name_student_trigger ON student;

触发器的行为

触发器函数有返回值。语句级触发器应该总是返回NULL，即必须显式地在触发器函数中写上“RETURN NULL”，如果没有写将导致报错，示例如下：

触发器函数中的特殊变量

当把一个PL/pgSQL函数当作触发器函数调用时，系统会在顶层声明段中自动创建几个特殊变量，比如在前面示例中的“NEW”“OLD”“TG_OP”变量等。可以使用的变量如下。

NEW：该变量为INSERT/UPDATE操作触发的行级触发器中存储新的数据行，数据类型是“RECORD”。在语句级别的触发器中此变量未分配，DELETE操作触发的行级触发器中此变量也未分配。
OLD：该变量为UPDATE/DELETE操作触发的行级触发器中存储原有的数据行，数据类型是“RECORD”。在语句级别的触发器中此变量未分配，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_RELNAME：触发器所在表的名称，该变量即将废弃，建议使用TG_TABLE_NAME变量来替换此变量。
TG_TABLE_NAME：触发器所在表的名称。
TG_TABLE_SCHEMA：触发器所在表的模式。
TG_NARGS：CREATE TRIGGER语句中赋予触发器过程参数的个数。
TG_ARGV[]：为text类型的数组；是CREATE TRIGGER语句中的参数。

事件触发器

PostgreSQL从9.3版开始支持一种称为“Event Trigger”的触发器，这种触发器主要用于弥补PostgreSQL以前版本不支持DDL触发器的不足。目前，事件触发器支持以下3种DDL事件。

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

由于事件触发器涉及的权限较大，比如能禁止DDL操作等，所以只有超级用户才能创建和修改事件触发器。

创建事件触发器

CREATE EVENT TRIGGER name
  ON event
  [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
  EXECUTE PROCEDURE function_name()

在创建事件触发器之前，必须先创建触发器函数，事件触发器函数的返回类型为event_trigger，注意，其与普通触发器函数的返回类型（trigger）是不一样的。

@TODD