PostGreSQL创建触发器

PostGreSQL创建触发器

PostGreSQL创建触发器跟其他数据库写法很不一样。
得先创建一个触发器函数。然后再创建触发器调用这个触发器函数。
只能这么干。没法直接写出一个触发器的全部内容。

PostgreSQL 触发器的创建分为两步：

一，使用 CREATE FUNCTION 语句创建一个触发器函数；

CREATE [ OR REPLACE ] FUNCTION trigger_function ()
  RETURNS trigger
AS $$
DECLARE
  declarations
BEGIN
  statements;
  ...
END; $$
LANGUAGE plpgsql

二，使用 CREATE TRIGGER 语句将该函数与表进行关联。

CREATE TRIGGER trigger_name 
{BEFORE | AFTER | INSTEAD OF} {event [OR ...]}
   ON table_name
   [FOR [EACH] {ROW | STATEMENT}]
   [WHEN ( condition ) ]
   EXECUTE FUNCTION trigger_function;

案例：

接下来我们通过触发器来实现记录员工的信息变更历史，首先创建一个历史记录表 employees_history：

create table employees_history (
    id serial primary key,
    employee_id int null,
    first_name varchar(20) null,
    last_name varchar(25) null,
    email varchar(25) null,
    phone_number varchar(20) null,
    hire_date date null,
    job_id varchar(10) null,
    salary numeric(8,2) null,
    commission_pct numeric(2,2) null,
    manager_id int null,
    department_id int null,
    action_type varchar(10) not null,
    change_dt timestamp not null
);

然后定义一个触发器函数 track_employees_change：

create or replace function track_employees_change()
  returns trigger as
$$
begin
  if tg_op = 'INSERT' then
    insert into employees_history(employee_id, first_name, last_name, email, phone_number, 
                                  hire_date, job_id, salary, commission_pct, manager_id, 
                                  department_id, action_type, change_dt)
    values(new.employee_id, new.first_name, new.last_name, new.email, new.phone_number, 
           new.hire_date, new.job_id, new.salary, new.commission_pct, new.manager_id, 
           new.department_id, 'INSERT', current_timestamp);
  elsif tg_op = 'UPDATE' then
    insert into employees_history(employee_id, first_name, last_name, email, phone_number, 
                                  hire_date, job_id, salary, commission_pct, manager_id, 
                                  department_id, action_type, change_dt)
    values(old.employee_id, old.first_name, old.last_name, old.email, old.phone_number, 
           old.hire_date, old.job_id, old.salary, old.commission_pct, old.manager_id, 
           old.department_id, 'UPDATE', current_timestamp);
  elsif tg_op = 'DELETE' then
    insert into employees_history(employee_id, first_name, last_name, email, phone_number, 
                                  hire_date, job_id, salary, commission_pct, manager_id, 
                                  department_id, action_type, change_dt)
    values(old.employee_id, old.first_name, old.last_name, old.email, old.phone_number, 
           old.hire_date, old.job_id, old.salary, old.commission_pct, old.manager_id, 
           old.department_id, 'DELETE', current_timestamp); 
  end if;

  return new;
end; $$
language plpgsql;

该函数根据不同的操作记录了相应的历史信息、操作类型和操作时间。

最后创建一个触发器 trg_employees_change，将该函数与 employees 进行关联：

create trigger trg_employees_change
  before insert or update or delete
  on employees
  for each row
  execute function track_employees_change();