postgresql使用触发器函数监控表中数据的改变

原文地址：postgresql使用触发器函数监控表中数据的改变

PL/pgSQL可以用于定义触发器过程。一个触发器过程是用CREATE FUNCTION命令创建的，创建的形式是一个不接受参数并且返回trigger类型的函数。请注意该函数即使在CREATE TRIGGER声明里声明为准备接受参数，它也必需声明为无参数，因为触发器的参数是通过TG_ARGV传递的，触发器函数里面可以使用的参数如下：

PL/触发器函数参数列表
参数名	描述
NEW	数据类型是RECORD；该变量为行级触发器中的INSERT/UPDATE操作存储新数据行。在语句级别的触发器里这个变量以及DELETE操作未赋值
OLD	数据类型是RECORD；该变量为行级触发器中的UPDATE/DELETE操作存储旧数据行。在语句级别的触发器里以及对INSERT动作，这个变量未赋值
TG_NAME	数据类型是name；该变量包含实际触发的触发器名
TG_WHEN	数据类型是text；是一个由触发器定义决定的字符串 (BEFORE, AFTER或者INSTEAD OF)
TG_LEVEL	数据类型是text；是一个由触发器定义决定的字符串(ROW或者STATEMENT)
TG_OP	数据类型是text；是一个说明激活触发器的操作的字符串 (INSERT, UPDATE，DELETE或者TRUNCATE)
TG_RELID	数据类型是oid；是激活触发器调用的表的对象标识(OID)
TG_RELNAME	数据类型是name；是激活触发器调用的表的名称。反对使用，并会在将来的版本中消失，推荐使用TG_TABLE_NAME
TG_TABLE_SCHEMA	数据类型是name；是激活触发器调用的表的模式名
TG_TABLE_NAME	数据类型是name；是激活触发器调用的表的名称
TG_NARGS	数据类型是integer；是在CREATE TRIGGER语句里面赋予触发器过程的参数的个数
TG_ARGV[]	数据类型是text的数组；是CREATE TRIGGER语句里的参数。下标从0开始记数。非法下标(小于0或者大于等于tg_nargs)导致返回一个NULL值

一个触发器函数必须返回NULL或者是一个与激活触发器运行的表的记录/行结构完全相同的数据

因BEFORE触发的行级别触发器可以返回一个NULL，告诉触发器管理器忽略对该行剩下的操作，也就是说，随后的触发器将不再执行，并且不会对该行产生INSERT/UPDATE/DELETE动作)。如果返回了一个非NULL的行，那么将继续对该行数值进行处理。请注意，返回一个和原来的NEW不同的行数值将修改那个将插入或更新的行因此，如果想在没有修改行值的同时成功的执行触发器动作，那么需要返回NEW（或等价的）。为了修改行存储，可以用一个值直接代替NEW里的某个数值并且返回之，或者也可以构建一个全新的记录/行再返回。在DELETE上的before触发器的情况下，返回值没有直接的影响，但是它不得不是非null以允许触发器操作继续执行。请注意DELETE触发器中NEW是null，因此返回往往是不明智的。DELETE触发器通常情况返回OLD。

INSTEAD OF触发器（总是行级触发器，并且只能用于视图）可以返回null标记他们不执行任何更新，并且应该忽略这些行操作的剩余部分（比如，随后的触发器不会被触发，并且为了周围的INSERT/UPDATE/DELETE在受影响的行状态下不计算行）。另外应该返回一个空值，用来标记触发器执行所需要的操作。为了 INSERT和UPDATE操作，返回值应是NEW，这个触发器函数可以修改以支持INSERT RETURNING和UPDATE RETURNING （这也将影响传递到任何随后触发器的行值）。为了DELETE操作，返回值应是OLD。

一个AFTER行级别的触发器或者 BEFORE或者AFTER语句级别的触发器返回值将总是被忽略；它们也可以返回NULL来忽略返回值。不过，任何这种类型的触发器仍然可以通过抛出一个错误来退出整个触发器操作。

贴上我个人测试的代码：

create or replace function monitor_table_dml() returns trigger
as $monitor_table_dml$
declare
	data_old VARCHAR(255);
	data_new VARCHAR(255);
	action_s VARCHAR(16);
	tablename VARCHAR(255);
	createdtime TIMESTAMP;
begin
--初始化表名
tablename=TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;
if (TG_OP='DELETE') then
	action_s='D';
	data_old=OLD;
elsif (TG_OP='UPDATE') then
	action_s='U';
	data_old=OLD;
	data_new=NEW;
elsif (TG_OP='INSERT') then
	action_s='I';
	data_new=NEW;
end if;
	
-- 更新数据到汇总表
<<insert_update>>
loop
	begin
		insert into db_op_log (
		olddata,
		newdata,
		action,
		tablename,
		createdtime)
		values(
		data_old,
		data_new,
		action_s,
		tablename,
		now()
		);
		exit insert_update;
	exception
		when unique_violation then
			-- 什么也不做
	end;
end loop insert_update;
return null;
end;
$monitor_table_dml$ language plpgsql;




--创建触发器
create trigger monitor_batches_ddl
after insert or update or delete on batches
	for each row execute procedure monitor_table_dml();

秒客网

postgresql使用触发器函数监控表中数据的改变

相关文章