postgresql触发器实操

什么是触发器

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

触发器在数据库中的应用非常广泛，例如在发生insert, update, delete, truncate时触发，调用事先写好的触发器函数。

PostgreSQL 触发器可以在下面几种情况下触发：

• BEFORE：在执行操作之前（在检查约束并尝试插入、更新或删除之前）；
• AFTER：在执行操作之后（在检查约束并插入、更新或删除完成之后）；
• INSTEAD OF：更新操作（在对一个视图进行插入、更新、删除时）。

语法

创建触发器

CREATE  TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_nameON table_name[ -- 触发器逻辑....];
复制

event_name 可以是在所提到的表 table_name 上的 INSERT、DELETE 和 UPDATE 数据库操作。

删除触发器

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

列出触发器

SELECT * FROM pg_trigger;
复制

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

实例

BEFORE

建表

先创建一张简单的表：

CREATE TABLE emp (    empname text,    salary integer,    last_date timestamp,    last_user text);
复制

创建函数

创建一个当我们触发器被触发时要执行的函数：

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$    BEGIN        -- 检查给出了 empname 以及 salary        IF NEW.empname IS NULL THEN            RAISE EXCEPTION 'empname cannot be null';        END IF;        IF NEW.salary IS NULL THEN            RAISE EXCEPTION '% cannot have null salary', NEW.empname;        END IF;        -- 谁会倒贴钱为我们工作？        IF NEW.salary < 0 THEN            RAISE EXCEPTION '% cannot have a negative salary', NEW.empname;        END IF;        -- 记住谁在什么时候改变了工资单        NEW.last_date := current_timestamp;        NEW.last_user := current_user;        RETURN NEW;    END;$emp_stamp$ LANGUAGE plpgsql;
复制

这个函数的主要作用是对表数据进行检查，以及记录下执行操作的时间和用户。

创建触发器

创建一个触发器：

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
复制

触发器在每次执行插入或更新表emp前触发。

插入数据

让我们插入个数据，来使触发器被触发。

INSERT INTO emp (empname,salary)VALUES ( 'Paul', 10000);
复制

查看数据

查看数据检测触发器是否执行成功。

SELECT * FROM emp;
复制

结果:

 empname | salary |         last_date          | last_user---------+--------+----------------------------+----------- Paul    |  10000 | 2021-08-12 15:53:23.980749 | memfire
复制

可以看到last_date和last_user被记录了下来。说明我们的触发器执行成功！

AFTER

建表

创建两张表：

CREATE TABLE company(   id INT PRIMARY KEY     NOT NULL,   name           TEXT    NOT NULL,   age            INT     NOT NULL,   address        CHAR(50),   salary         REAL); CREATE TABLE audit(   emp_id INT NOT NULL,   entry_date TEXT NOT NULL);
复制

创建函数

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;
复制

函数的主要作用是将id和当前时间插入audit表中。

创建触发器

CREATE TRIGGER example_trigger AFTER INSERT ON company FOR EACH ROW EXECUTE PROCEDURE auditlogfunc();
复制

当插入表company后执行触发器，将company的id和当前时间插入audit表中。

插入数据

INSERT INTO company (id,name,age,address,salary) VALUES (1, 'Paul', 32, 'California', 20000.00 );
复制

插入数据使触发器执行

查看数据

查看audit表数据，检测触发器是否执行成功

SELECT * FROM audit;
复制

结果：

 emp_id |          entry_date--------+-------------------------------      1 | 2021-08-12 16:16:01.865973+08(1 row)
复制

触发器执行成功！

INSTEAD OF

建表

create table tb (id int, info text, crt_time timestamp);
复制

创建视图

create view v_tb as select * from tb;
复制

创建函数

create or replace function tg() returns trigger as $$declarebegin  case TG_OP  when 'INSERT' then    NEW.id := NEW.id+1;    raise notice '%, %, %, %, new:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW;    return NEW;  when 'UPDATE' then     NEW.id := NEW.id+1;    OLD.id := OLD.id+1;    raise notice '%, %, %, %, new:%, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, NEW, OLD;    return NEW;  when 'DELETE' then    OLD.id := OLD.id+1;    raise notice '%, %, %, %, old:%', TG_OP, TG_NAME, TG_WHEN, TG_LEVEL, OLD;    return OLD;  end case;end;

$$
 language plpgsql;

复制

创建触发器

create trigger tg1 instead of insert or update or delete on v_tb for each row execute procedure tg();

复制

当我们对v_tbl视图进行插入、更新或删除时执行对应的操作

插入数据

insert into v_tb values (1, 'digoal', now()) returning *;

复制

插入数据使触发器执行。因为我们这个sql
直接返回了结果，可以直接查看：

 id |  info  |          crt_time
----+--------+----------------------------
  2 | digoal | 2021-08-12 16:26:55.494145
(1 row)

复制

id变成了2，说明我们的触发器执行成功！

总结

上面用简单的三个例子分别对三种触发器进行了说明。当然实际项目中情况往往比这复杂得多，但不管怎样，终归是由这些基础构成的！

强烈建议我们在学习这些基础SQL操作的时候一定要多敲几遍。看懂了不代表自己就真的懂了。