触发器创建示例

2019-03-30

摘抄自《posrgreSQL 修炼之道从小工到专家》

触发器（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 ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ 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_no int,
    math_score int,
    test_date date
)

如果想删除学生表的一条记录时，把这个学生在成绩表中的成绩也删除掉，这时就可以使用触发器。先建触发器的执行函数：

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();

测试

按照上面的语句创建好触发器后还需要相应的测试，先插入一些测试数据：

INSERT INTO student VALUES(1, '张三', 14);
INSERT INTO student VALUES(2, '李四', 13);
INSERT INTO student VALUES(3, '王二', 15);

INSERT INTO score VALUES(1, 85, 75, date '2013-05-23');
INSERT INTO score VALUES(1, 89, 73, date '2013-09-18');
INSERT INTO score VALUES(2, 68, 83, date '2013-05-23');
INSERT INTO score VALUES(2, 73, 85, date '2013-09-18');
INSERT INTO score VALUES(3, 72, 79, date '2013-05-23');
INSERT INTO score VALUES(3, 78, 82, date '2013-05-23');

现在把学好为 3 的学生 “王二” 从表 “student” 删掉：

DELETE FROM stduent WHERE student_no = 3;

这时可以查询成绩表 ‘score’ 可以发现学号（student_no ）为 3 的学生成绩记录也被删除掉了

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

语句级触发器

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

假设对 student 的更新情况记录 log。可以为 student 建一张 log 表，如下：

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

创建记录 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 UPDATE OR DELETE ON student
    FOR STATEMENT
    EXECUTE PROCEDURE log_student_trigger();

删除触发器

drop trigger log_student_log on student;

语句级触发器即使在没有更新到数据时，也会被触发

行级触发器

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

行级触发器即使在没有更新到数据时，不会被触发

BEFORE 触发器和 AFTER 触发器

通常，语句级别的 “before” 触发器是在语句开始做任何事之前被触发，而语句级别的"after" 触发器是在语句结束时才触发的。行级别的"before" 触发器是在对特定行进行操作之前触发的，而行级别的 “after” 触发器是在语句结束时才触发的，但是它会在任何语句级别的 “after” 触发器被触发之前触发

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

先建一个触发器函数：

CREATE FUNCTION student_new_name_trigger()
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 触发器，因为 BEFORE 触发器是在更新数据之前触发的，所以这时修改了"NEW.student_name", 后面实际更新到数据库中的值就变成了 “student_name||student_no”

如果使用了 AFTER ，则修改 “NEW” 是没用的

删除触发器

删除触发器的语法如下：

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

其中的语法说明如下。

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

**在 PostgresSQL 中要在删除触发器的语法中指定 “ON table”，而在其他一些数据库的语发可能直接是 “DROP TRIGGER name” **

删除触发器时，触发器的函数不会被删除。不过，当表删除时，表上的触发器也会被删除

触发器的行为

触发器函数与返回值。语句级触发器总是返回 NULL。即必须显式的在触发器函数中写上 “RETURN NULL”, 如果没有写，将导致出错。

对于 “BEFORE” 和 “INSTEAD OF” 这类行级触发器函数来说，如果返回的是 NULL，则表示忽略对当前行的操作，如果返回的是非 NULL 行，对与 INSERT 和 UPDATE 来说，返回的行将成为被插入的行或将要是更新的行。

对于　 AFTER 这类行级触发器来说，其返回值将会被忽略。

如果同一时间上有多个触发器，则将按触发器名字的顺序来触发。　如果是　“BEFORE” 和　“INTEAD OF” 行级触发器，每个触发器所返回的行（可能已经被修改）将成为下一个触发器的输入，如果"BEFORE" 和　“INSTEAD OF” 行级触发器返回的内容为空，那么该行上的其他行级触发器也不会被触发。

触发器函数中的特殊变量

当把一个 PL/pgSQL 函数当做触发器函数调用的时候，系统会在顶层生命字段里自动创建几个特殊变量，比如在之前的几个例子当中　“NEW”, “OLD”, “TG_OP”, 变量等。可以使用的变量如下这些：

NEW: 该变量为 INSERT/UPDATE 操作触发的行级触发器中存储的新数据行，数据类型是　“RECORD” ,在语句级别的触发器里没有分配次变量，　 DELETE 操作触发的行级触发器中也没有分配此变量
OLD：数据类型是 record。在 update、delete 操作触发时存储旧的数据行。
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 类型的一个数组。创建触发器语句中指定的参数。