create table <tablename>_log(
id serial primary key,
idx integer,
antes json,
despues json,
id_m_usuario integer,
fecha_registro timestamp default now(),
tipo char(1) default 'U',
unico varchar(36)
);
create function f_<tablename>_log()
returns trigger
LANGUAGE plpgsql as
$body$
begin
case tg_op
when 'INSERT' then
insert into <tablename>_log (idx, despues, tipo)
select new.id, row_to_json(new), 'I';
return new;
when 'UPDATE' then
if (.. <condiciones new.campo1 <> new.campo2>... ) then
insert into <tablename>_log (idx, antes, despues, tipo)
select new.id, row_to_json(old), row_to_json(new), 'U';
end if;
return new;
when 'DELETE' then
insert into <tablename>_log (idx, antes, tipo)
select new.id, row_to_json(old), 'D';
return old;
end case;
end;
$body$;
create trigger t_<tablename>_log
before insert or update or delete
on <tablename>
for each row execute procedure f_<tablename>_log();
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter