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