什么是触发器
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操作的时候一定要多敲几遍。看懂了不代表自己就真的懂了。
