Saludos,
Me gustaria saber que opinion tienen sobre usar hstore para llevar una
auditoria de las tablas de la base de datos de la compañia de trabajo.
--CREATE EXTENSION hstore;
DROP TABLE IF EXISTS auditoria;
create table auditoria
(
fechahora timestamp not null,
usuario text not null,
operacion text not null,
nombre_esquema text not null,
nombre_tabla text not null,
antes hstore,
despues hstore
);
CREATE OR REPLACE FUNCTION f_auditoria()
RETURNS trigger AS
$$
begin
IF TG_OP = 'INSERT' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema,
nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP,
TG_TABLE_SCHEMA,TG_TABLE_NAME,NULL, hstore(new);
RETURN NEW;
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema,
nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP,
TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), hstore(new);
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
INSERT INTO auditoria(fechahora, usuario, operacion, nombre_esquema,
nombre_tabla, antes, despues)
SELECT CURRENT_TIMESTAMP, user, TG_OP,
TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), NULL;
RETURN OLD;
END IF;
end;
$$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE OR REPLACE FUNCTION f_colocar_auditoria(text)
RETURNS void AS
$$
DECLARE
tabla ALIAS FOR $1;
nombretrigger text;
sql text;
BEGIN
nombretrigger = 'trigger_auditoria_' || replace (tabla,'.','_');
sql = 'CREATE TRIGGER ' || nombretrigger || ' AFTER INSERT OR UPDATE OR
DELETE ON '|| tabla || ' FOR EACH ROW EXECUTE PROCEDURE f_auditoria()';
EXECUTE sql;
END;
$$
LANGUAGE plpgsql VOLATILE
COST 100;
------------------------------------------------- ejemplo
-------------------------------------------
DROP TABLE IF EXISTS producto;
CREATE TABLE producto
(
idproducto serial NOT NULL,
nombre text NOT NULL,
costo numeric (10,2) NOT NULL,
stock integer NOT NULL,
PRIMARY KEY (idproducto)
);
select f_colocar_auditoria('producto');
DROP TABLE IF EXISTS cliente;
CREATE TABLE cliente
(
idcliente serial not null,
nombre text not null,
direccion text not null,
primary key(idcliente)
);
select f_colocar_auditoria('cliente');
insert into producto (nombre, costo, stock) values ('Monitor', 100.23, 10);
insert into producto (nombre, costo, stock) values ('Teclado', 30, 5);
insert into producto (nombre, costo, stock) values ('Mouse', 25, 50);
update producto set costo=99 where idproducto=1;
update producto set stock=30 where idproducto=3;
update producto set costo=90, stock=8 where idproducto=1;
delete from producto where idproducto=2;
insert into cliente (nombre, direccion) values ('Pepe', 'Bogota');
insert into cliente (nombre, direccion) values ('María', 'Caracas');
insert into cliente (nombre, direccion) values ('Jose', 'Buenos Aires');
update cliente set Nombre='José' where idcliente=3;
update cliente set direccion='Quito' where idcliente=1;
delete from cliente where idcliente=3;
-- pregunta nro 1, listar la auditoria del producto codigo 1
select * from auditoria where nombre_tabla = 'producto'
and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1') order by
fechahora desc;
-- pregunta nro 2, listar la auditoria del producto codigo 2
select * from auditoria where nombre_tabla = 'producto'
and (antes -> 'idproducto' = '2' or despues -> 'idproducto' = '2') order by
fechahora desc;
-- pregunta nro 3 listar la auditoria del cliente codigo 3
select * from auditoria where nombre_tabla = 'cliente'
and (antes -> 'idcliente' = '3' or despues -> 'idcliente' = '3') order by
fechahora desc;
--pregunta nro 4. cuales fueron los campos actualizados en el producto nro
1 y cuales eran sus valores antiguos y nuevos
select fechahora, akeys(antes-despues) as campos, avals(antes-despues) as
antiguo, avals(despues-antes) as nuevo from auditoria
where operacion='UPDATE' AND nombre_tabla = 'producto'
and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1') order by
fechahora desc;
