postgresql 触发器——迹忆客-ag捕鱼王app官网

postgresql 触发器是数据库的回调函数，它会在指定的数据库事件发生时自动执行/调用。

下面是关于 postgresql 触发器几个比较重要的点：

• postgresql 触发器可以在下面几种情况下触发：
  • 在执行操作之前（在检查约束并尝试插入、更新或删除之前）。
  • 在执行操作之后（在检查约束并插入、更新或删除完成之后）。
  • 更新操作（在对一个视图进行插入、更新、删除时）。
• 触发器的 for each row 属性是可选的，如果选中，当操作修改时每行调用一次；相反，选中 for each statement，不管修改了多少行，每个语句标记的触发器执行一次。
• when 子句和触发器操作在引用 new.column-name 和 old.column-name 表单插入、删除或更新时可以访问每一行元素。其中 column-name 是与触发器关联的表中的列的名称。
• 如果存在 when 子句，postgresql 语句只会执行 when 子句成立的那一行，如果没有 when 子句，postgresql 语句会在每一行执行。
• before 或 after 关键字决定何时执行触发器动作，决定是在关联行的插入、修改或删除之前或者之后执行触发器动作。
• 要修改的表必须存在于同一数据库中，作为触发器被附加的表或视图，且必须只使用 tablename，而不是 database.tablename。
• 当创建约束触发器时会指定约束选项。这与常规触发器相同，只是可以使用这种约束来调整触发器触发的时间。当约束触发器实现的约束被违反时，它将抛出异常。

语法

创建触发器时的基础语法如下：

create  trigger trigger_name [before|after|instead of] event_name
on table_name
[
 -- 触发器逻辑....
];

在这里，event_name 可以是在所提到的表 table_name 上的 insert、delete 和 update 数据库操作。您可以在表名后选择指定 for each row。

以下是在 update 操作上在表的一个或多个指定列上创建触发器的语法：

create  trigger trigger_name [before|after] update of column_name
on table_name
[
 -- 触发器逻辑....
];

示例

让我们假设一个情况，我们要为被插入到新创建的 company 表（如果已经存在，则删除重新创建）中的每一个记录保持审计试验：

jiyik_db=# create table company(
   id int primary key     not null,
   name           text    not null,
   age            int     not null,
   address        char(50),
   salary         real
);

为了保持审计试验，我们将创建一个名为 audit 的新表。每当 company 表中有一个新的记录项时，日志消息将被插入其中：

jiyik_db=# create table audit(
   emp_id int not null,
   entry_date text not null
);

在这里，id 是 audit 记录的 id，emp_id 是来自 company 表的 id，date 将保持 company 中记录被创建时的时间戳。所以，现在让我们在 company 表上创建一个触发器，如下所示：

jiyik_db=# create trigger example_trigger after insert on company for each row execute procedure auditlogfunc();

auditlogfunc() 是 postgresql 一个程序，其定义如下：

create or replace function auditlogfunc() returns trigger as $example_table$
   begin
      insert into audit(emp_id, entry_date) values (new.id, current_timestamp);
      return new;
   end;
$example_table$ language plpgsql;

现在，我们开始往 company 表中插入数据：

jiyik_db=# insert into company (id,name,age,address,salary) values (1, 'paul', 32, 'california', 20000.00 );

这时，company 表中插入了一条记录：

同时， audit 表中也插入了一条记录，因为我们在插入 company 表时创建了一个触发器。相似的，我们也可以根据需求在更新和删除时创建触发器：

emp_id |          entry_date
-------- -------------------------------
      1 | 2013-05-05 15:49:59.968 05:30
(1 row)

列出触发器

你可以把从 pg_trigger 表中把当前数据库所有触发器列举出来：

jiyik_db=# select * from pg_trigger;

如果，你想列举出特定表的触发器，语法如下：

jiyik_db=# select tgname from pg_trigger, pg_class where tgrelid=pg_class.oid and relname='company';

结果如下：

 tgname
-----------------
 example_trigger
(1 row)

删除触发器

删除触发器基础语法如下：

drop trigger ${trigger_name} on ${table_of_trigger_dependent};

删除本文上表 company 上的触发器 example_trigger 的指令为：

drop trigger example_trigger on company;