触发器创建示例

摘抄自 《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 类型的一个数组。创建触发器语句中指定的参数。