postgresql利用事件触发器记录表的创建和删除时间

最新推荐文章于 2025-09-10 09:53:09 发布

原创最新推荐文章于 2025-09-10 09:53:09 发布 · 927 阅读

1 ·

CC 4.0 BY-SA版权

postgresql 专栏收录该内容

61 篇文章

订阅专栏

本文介绍了如何在PostgreSQL中使用事件触发器来记录表的创建和删除操作，以便进行事后审计。首先创建了一个drop_audit表用于存储审计信息，然后定义了两个触发器函数：event_trigger_drop_function()用于记录drop操作，event_trigger_ddl_commands_function()用于记录create table操作。这两个函数分别在'droptable'和'createtable'事件发生时触发，将相关信息插入到drop_audit表中。通过测试，证实了触发器能够成功记录这些操作。

在postgresql中有一种触发器，叫做事件触发器，我们可以通过这个来记录表的创建和删除时间等。

1.创建一个事件触发器，记录所有的drop table操作用于事后审计

记录删除的事件触发器，利用到了一个系统函数pg_event_trigger_dropped_objects()

函数结构如下：

1)创建表，用于记录drop操作

create table drop_audit(
classid     	oid,
objid	   		oid,
objsubid	 	int,
object_type  	text,
schema_name  	text,
object_name  	text,
object_identity  text,
ddl_tag 	 	text,
op_time 		timestamp
);

2)创建触发器函数

create or replace function event_trigger_drop_function()
	returns event_trigger
	language plpgsql
AS $$
	declare
		obj record;
	begin
		insert into drop_audit
		select classid,objid,objsubid,object_type,schema_name,
		object_name,object_identity,tg_tag,now()
		from pg_event_trigger_dropped_objects();
	end;
$$;

3)创建触发器

create event trigger drop_event_trigger on sql_drop  when tag in ('drop table')
execute procedure event_trigger_drop_function();

4)测试：删除表

可以看到，删除操作已经记录在了drop_audit的表中了

2.接下来创建一个触发器，用户记录create table操作

记录create table的触发器用到了pg_event_trigger_ddl_commands()函数

1)为了方便，还是直接使用上面的drop_audit这个表

2)创建触发器函数

create or replace function event_trigger_ddl_commands_function()
	returns event_trigger
	language plpgsql
AS $$
	declare
		obj record;
	begin
		insert into drop_audit
		select classid,objid,objsubid,object_type,schema_name,
		'',object_identity,tg_tag,now()
		from pg_event_trigger_ddl_commands();
	end;
$$;

3)创建出触发器

create event trigger ddl_event_trigger on ddl_command_end  when tag in ('create table')
execute procedure event_trigger_ddl_commands_function();

4)测试

同样，create table的操作也会记录进去