postgresql 触发器

  • 2021-06-29
  • 浏览 (304)

触发器是某个数据库操作发生时被自动调用的函数。可以在INSERT、UPDATE、DELETE、TRUNCATE操作之前或之后调用触发器。postgre 的触发器分为普通触发器和事件触发器,普通触发器针对dml,事件触发器针对ddl。

普通触发器

有两种类型,语句级触发器和行级触发器

  • 语句级触发器:指执行每个SQL时只执行一次。关键字 for each statement。

  • 行级触发器:指每行都会执行一次。关键字for each row。

对于数据行级的触发器,触发发触发器的语句每操作一个数据行,它就被执行一次。对于语句级的触发器,它只会被执行一次。

语法

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name  
ON table_name  
[  
 -- Trigger logic goes here....  
];

在这里,event_name可以是INSERTUPDATEDELETETRUNCATE数据库操作上提到的表table_name。 您可以选择在表名后指定FOR EACH ROWfor each statement

内置变量

当一个PL/pgSQL 函数作为一个触发器被调用时,系统自动在最外层的块创建一些特殊的变量。这些变量分别是:
1)NEW
数据类型是RECORD。对于行级触发器,它存有INSERT或UPDATE操作产生的新的数据行。对于语句级触发器,它的值是NULL。

2)OLD
数据类型是RECORD。对于行级触发器,它存有被UPDATE或DELETE操作修改或删除的旧的数据行。对于语句级触发器,它的值是NULL。

3)TG_NAME
数据类型是name,它保存实际被调用的触发器的名字。

4)TG_WHEN
数据类型是text,根据触发器定义信息的不同,它的值是BEFORE 或AFTER。

5)TG_LEVEL
数据类型是text,根据触发器定义信息的不同,它的值是ROW或STATEMENT。

6)TG_OP
数据类型是text,它的值是INSERT、UPDATE或DELETE,表示触发触发器的操作类型。

7)TG_RELID
数据类型是oid,表示触发器作用的表的oid。

8)TG_RELNAME
数据类型是name,表示触发器作用的表的名字。它与下面的变量TG_TABLE_NAME的作用是一样的。

9)TG_TABLE_NAME
数据类型是name,表示触发器作用的表的名字。

10)TG_TABLE_SCHEMA
数据类型是name,表示触发器作用的表所在的模式。

11)TG_NARGS
数据类型是integer,表示CREATE TRIGGER命令传给触发器过程的参数的个数。

12)TG_ARGV[]
数据类型是text类型的数组。表示CREATE TRIGGER命令传给触发器过程的所有参数。下标从0开始。TG_ARGV[0]表示第一个参数,TG_ARGV[1]表示第二个参数,以此类推。 如果下标小于0或大于等于tg_nargs,将会返回一个空值。

样例

create table student(
student_no int primary key,
student_name varchar(40),
age int);

create table opt_log(update_time timestamp,db_user varchar(40),schema_name varchar(40),table_name varchar(40), opr_type varchar(40));

drop function opt_log_trigger;
create function opt_log_trigger() 
returns trigger as
$$
begin
    insert into opt_log values(now(),user,TG_TABLE_SCHEMA,TG_TABLE_NAME,TG_OP);
return NULL;
END;
$$
language "plpgsql";


---语句级触发器,每条语句只记录一次
drop trigger opt_log_trigger on student;
create trigger opt_log_trigger after insert or delete or update or truncate on student for statement execute procedure opt_log_trigger();


insert  into student values(1,'zhangsan', 14),(2,'lisi', 15);

truncate student;

事件触发器

postgresql从9.3开始支持事件触发器,也就是DDL触发器。目前有以下三种:

ddl_command_start、ddl_command_end、sql_drop。

分别对应DDL开始执行前、执行后,以及删除数据库对象前触发。

且这种触发器很危险,只有超级管理员才可以创建。

使用触发器的优点

  • 它提高了应用程序的开发速度。 因为数据库存储触发器,所以您不必将触发器操作编码到每个数据库应用程序中。
  • 全局执法业务规则。定义触发器一次,然后将其重用于使用数据库的任何应用程序。
  • 更容易维护 如果业务策略发生变化,则只需更改相应的触发程序,而不是每个应用程序。
  • 提高客户/服务器环境的性能。 所有规则在结果返回之前在服务器中运行。
正文到此结束