通过触发器方式获取表最后更新时间，并将时间信息写入到另外一张表

一、创建测试表和表记录更新时间表

CREATE TABLE weather(city varchar(80),temp_lo int,  --最低温度temp_hi int,  --最高温度prcp real,     --湿度date date);

CREATE TABLE t_record_change(table_name varchar(32) primary key,last_update_time timestamp(6) with time zone,trans_id varchar(32),commited numeric(1,0));

二、创建触发器函数

--创建触发器函数create or replace function f_update_change_log() returns trigger as $$begin    insert into t_record_change(table_name,last_update_time,trans_id,commited) values(TG_TABLE_NAME,current_timestamp,(select txid_current()),1)        on conflict(table_name)    do update set last_update_time = current_timestamp,trans_id = (select txid_current()),commited = 1;    return null;end;$$ language plpgsql;

三、创建触发器

--创建触发器drop trigger if exists x_weather_u on weather;create trigger x_weather_u after insert or update or delete on weatherfor each statement execute procedure f_update_change_log();

四、测试

在sql窗口中分别执行以下sql语句，并到t_record_change表中查看时间是否更新

insert into weather values('nanjing',20,40,0.28,'2018-06-27');update weather set temp_lo = 15 where city = 'nanjing';delete from weather where city = 'nanjing';