sql中对插删改操作进行跟踪记录，并返回影响的行数

思路是先创建个表进行必要的信息记录：
 

create table if not exists Data_Trace (
TableName Name not null,--引起触发器的表格名称
User_name Name not null,
Schema_name Name not null,
Op_time timestamp,
Op character,--操作类型代码
XID int8 not null,--获取目前操作的ID
count_affected_rows int8 default 1
);

然后是触发器函数主体：

CREATE OR REPLACE FUNCTION recordchange2() RETURNS TRIGGER AS
$$
declare op_record character;
		op_time_record timestamp;
		xid_record int8;
BEGIN
create table "temp".Data_Trace_1 (
TableName Name not null,--引起触发器的表格名称
User_name Name not null,
Schema_name Name not null,
Op_time timestamp,
Op character,--操作类型代码
XID int8 not null,--获取目前操作的ID
count_affected_rows int8 default 1
);
IF (TG_OP = 'DELETE') THEN
	insert into "temp".Data_Trace_1 ( TableName,User_name,Schema_name,Op_time,Op, XID) 
	values ( TG_RELID::regclass,session_user,TG_TABLE_SCHEMA,now(),'d', txid_current());
	if (select count(*) from public.data_trace as a,"temp".Data_Trace_1 
	where a.op_time="temp".Data_Trace_1.op_time and a.op="temp".Data_Trace_1.op
	and a.xid="temp".Data_Trace_1.xid)<>0 then
		select op,op_time,xid from "temp".Data_Trace_1 into op_record,op_time_record,xid_record;
		update public.data_trace set count_affected_rows=count_affected_rows+1
		where op_time=op_time_record and op=op_record
				and xid=xid_record;
	else
		insert into public.data_trace
		select * from "temp".Data_trace_1;
	end if;
ELSIF (TG_OP = 'UPDATE') THEN
	insert into "temp".Data_Trace_1 ( TableName,User_name,Schema_name,Op_time,Op, XID) 
	values ( TG_RELID::regclass,session_user,TG_TABLE_SCHEMA,now(),'u', txid_current());
	if (select count(*) from public.data_trace as a,"temp".Data_Trace_1 
	where a.op_time="temp".Data_Trace_1.op_time and a.op="temp".Data_Trace_1.op
	and a.xid="temp".Data_Trace_1.xid)<>0 then
		select op,op_time,xid from "temp".Data_Trace_1 into op_record,op_time_record,xid_record;
		update public.data_trace set count_affected_rows=count_affected_rows+1
		where op_time=op_time_record and op=op_record
				and xid=xid_record;
	else
		insert into public.data_trace
		select * from "temp".Data_trace_1;
	end if;
ELSIF (TG_OP = 'INSERT') then
	insert into "temp".Data_Trace_1 ( TableName,User_name,Schema_name,Op_time,Op, XID) 
	values ( TG_RELID::regclass,session_user,TG_TABLE_SCHEMA,now(),'i', txid_current());
	if (select count(*) from public.data_trace as a,"temp".Data_Trace_1 
	where a.op_time="temp".Data_Trace_1.op_time and a.op="temp".Data_Trace_1.op
	and a.xid="temp".Data_Trace_1.xid)<>0 then
		select op,op_time,xid from "temp".Data_Trace_1 into op_record,op_time_record,xid_record;
		update public.data_trace set count_affected_rows=count_affected_rows+1
		where op_time=op_time_record and op=op_record
				and xid=xid_record;
	else
		insert into public.data_trace
		select * from "temp".Data_trace_1;
	end if;
END IF;
drop table "temp".Data_Trace_1;
RETURN NULL; -- result is ignored since this is an AFTER
END;
$$ LANGUAGE plpgsql;

测试用句

CREATE TABLE test1101(id int primary key ,
col1 varchar(20), 
col2 varchar(20), 
col3 timestamp without time zone);

CREATE TRIGGER test1101_tri_ins AFTER INSERT OR update or DELETE ON test1101
    FOR EACH ROW EXECUTE PROCEDURE recordchange2();
---------------test----------------------
insert into test1101 values (11,'aa','bb',now());insert into test1101 values (12,'aa','cc',now());
insert into test1101 values (13,'aa','cc',now());
insert into test1101 values (14,'aa','cc',now());
insert into test1101 values (15,'aa','cc',now());
insert into test1101 values (16,'aa','cc',now());
insert into test1101 values (17,'aa','cc',now());
insert into test1101 values (118,'aa','cc',now()),
(119,'aa','cc',now()),(110,'aa','cc',now()),
(121,'aa','cc',now()),(122,'aa','cc',now());
insert into test1101 values (19,'aa','cc',now());
update test1101 set col1='d',col2='mm',col3=now()
where id=11;
update test1101 set col1='d',col2='mm',col3=now()
where id=12;
update test1101 set col1='d',col2='mm',col3=now()
where id=13;
update test1101 set col1='d',col2='mm',col3=now()
where id=14;
update test1101 set col1='d',col2='mm',col3=now()
where id=15;
update test1101 set col1='d',col2='mm',col3=now()
where id=16;
update test1101 set col1='d',col2='mm',col3=now()
where id=17;
delete from test1101 where id =11;
delete from test1101 where id =12;
delete from test1101 where id =13;
delete from test1101 where id =14;
delete from test1101 where id =15;
delete from test1101 where id=6 and id=7 and id=8;