PostgreSQL——触发器

一、触发器和触发器函数

一个触发器是一种声明，告诉数据库应该在执行特定操作时执行特定的函数。触发器的执不需要使用CALL语句来调用，也不需要手工启动，只要当一个预定义的事件发生时PostgreSQL就会自动调用它。

触发器可以定义在一个NSERT、UPDATE或DELETE命令之前或者之后执行。如果定义INSERT之前，表明在数据库插入之前，先调用触发器，再执行触发器函数。

创建触发器函数的基本语法格式如下：

CREATE FUNCTION fun_name() RETURNS trigger as $fun_name$
	BEGIN
		函数执行代码;
	END:
$ fun_name $ LANGUAGE plpgsql;

其中，fun_name为触发器函数的名称。触发器函数创建完成后，可以使用CREATE TRIGGER创建触发器。触发器通常还分为BEFORE触发器和AFTER触发器。BEFORE触发器通常在语句开始做任何事情之前触发，AFTER触发器则在语句结束时触发。同一个触发器函数可以用于多个触发器。

二、创建触发器

-- 创建触发器语法
create trigger 触发器名 before | after 触发事件
on 表名 for each row execute procedure 触发器函数;

-- 创建测试表
create table timedb(
	uid integer,
	gid integer,
	uptime timestamp with time zone
);

-- 创建一个触发器，使得每次该表有新数据insert时，其中的一个时间字段uptime自动变为当前时间
-- 创建一个自定义触发器函数，此函数的主要目的是为了更新当前时间
create function func_timedb() returns trigger as $func_timedb$
	begin
		if (TG_OP = 'UPDATE') then
			if NEW.uptime = OLD.uptime then
				return null;
			end if;
		end if;
		update timedb set uptime = now() where uid = NEW.uid and gid = NEW.gid;
		return null;
	end;
$func_timedb$ language plpgsql;

-- 创建触发器
create trigger timedb_updateTime 
after insert on timedb
for each row execute procedure func_timedb();

-- 测试，插入数据
insert into timedb values(1, 3);

-- 查询数据
select * from timedb;

三、查看和修改触发器

-- 使用pg_trigger系统表
SELECT tgname AS trigger_name, 
       tgrelid::regclass AS table_name,
       tgfoid::regproc AS function_name,
       tgtype::integer AS trigger_type,
       tgenabled AS enabled
FROM pg_trigger
ORDER BY table_name, trigger_name;

-- 使用information_schema.triggers视图
SELECT trigger_name, 
       event_object_table AS table_name,
       event_manipulation AS trigger_event,
       action_timing AS timing,
       action_statement AS definition
FROM information_schema.triggers
WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_name, trigger_name;

-- 无法直接修改触发器，需先删除再新建
-- 假设我们要修改一个在 employees 表上的 update_employee_audit 触发器：


-- 查看当前触发器定义：
SELECT pg_get_triggerdef(oid) 
FROM pg_trigger 
WHERE tgname = 'update_employee_audit';

-- 删除旧触发器：
DROP TRIGGER update_employee_audit ON employees;

-- 创建修改后的新触发器：
CREATE TRIGGER update_employee_audit
AFTER UPDATE ON employees
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION log_employee_changes();

四、触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。

触发程序与表相关，当对表执行INSERT、DELETE或UPDATE语句时，将激活触发程序。可以将触发程序设置为在执行语句之前或之后激活。例如，可以在表中插入每一行之前，或在更新了每一行后激活触发程序。

-- 【例11.2】创建一个account表，然后创建一个触发器，用于检测account表的name列的插入数据是否为空。如果为空，将弹出错误提示。

-- 首先创建用于测试的数据库，包含两个字段id和name,SQL语句如下：
create table account(
	id int,
	name char(20)
);

-- 创建触发器函数，主要为了检测插入的name字段的数据是否为空
create function account_stam() returns trigger as $account_stam$
	begin
		if NEW.name is null then
			raise exception 'name 字段不能为空值';
		end if;
	end;
$account_stam$ language plpgsql;

-- 创建一个BEFORE触发器
create trigger account_stamp before insert
on account for each row execute procedure account_stam();

-- 测试插入数据
insert into account values(10);

五、删除触发器

使用DROP TRIGGER语句可以别除Postgr©SQL中已经定义的触发器，删除触发器语句的基本语法格式如下：

DROP TRIGGER trigger_name ON schema_name

其中，schema_name表示数据库名称，trigger_name表示要删除的触发器的名称.

-- 删除一个触发器
drop trigger timedb_updateTIme on timedb;

六、案例

-- 创建一个学生分数表studentl
create table student1(
	sutnum varchar(30) not null,
	major varchar (20) not null,
	score int not null
);

-- 创建一个学生状态表studentl_stats
create table student1_stats(
	major varchar (20) not null,
	total_score integer not null,
	total_students integer not null
);

-- 创建一个触发器函数，在更新过studentl表的数据后，更新studentlstats表中的数据
create or replace function fun_student_major() returns trigger as $body$ 
	declare rec record;
	begin	
		delete from student1_stats; --将统计表里面的旧数据清空
		for rec in (
			select major, sum(score) as total_score, count(*) as total_students
			from student1 
			group by major) loop
				insert into student1_stats values(rec.major, rec.total_score, rec.total_students);
		end loop;
		return NEW;
	end;
$body$ language 'plpgsql' volatile

-- 创建AFTER触发器，在更新studentl表的数据后启动.
create trigger fun_student_major after insert or update or delete 
on student1 for each row execute procedure fun_student_major();

-- 测试插入记录
insert into student1 
values('10010', '英语', 90),
('10011', '数学', 86),
('10012', '物理', 70),
('10013', '语文', 95);

-- 查看student1_stats表中记录变化
select * from student1_stats;

从执行的结果来看，在studentl表插入记录之后，触发器计算插入到studentl表中的数据，并将结果插入到studentl stats表中相应的位置。

-- 更新studentI表中的记录，将科目等于语文的分数改为89分
update student1 set score = 89 
where major = '语文';

-- 查看studentl stats表中记录的变化
select * from student1_stats;

-- 删除studentl表中的记录，将科目等于物理分数的记录删除
delete from student1 where major = '物理';

-- 查看studentl_stats表中记录的变化
select * from student1_stats;