I imagine that the use of Trigger would be generic for several tables, in this case I believe it is interesting to have a function to compare the json with the records. This way you can evolve the independent comparison function of Trigger.
I created an example using the postgres
10 and data type resources jsonb
, thus, I changed the data type of some table columns auditoria
of text
for jsonb
.
CREATE TABLE auditoria
(
id serial NOT NULL,
operacao character varying(50) NOT NULL,
data timestamp with time zone,
usuario character varying(50),
tabela character varying(50),
antes jsonb,
depois jsonb,
diferenca jsonb,
CONSTRAINT auditoria_pkey PRIMARY KEY (id)
);
create table produto (
codigo integer,
descricao text,
estoque numeric
);
insert into produto(codigo, descricao, estoque)
values(1, 'Filtro de óleo', 53), (2, 'Cerveja 600ml', 100);
The function below takes two jsonb
, which would be the old and new records respectively. Then a loop is made on the json keys which would be the table fields. If in the comparison of the two objects there is a difference it returns a new json.
CREATE or replace FUNCTION fn_compara_jsonb(antigo jsonb, novo jsonb)
RETURNS jsonb
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
declare
keys record;
jsonb_retorno jsonb = '{}'::jsonb;
begin
for keys in
select *
from jsonb_object_keys($1)
loop
if $1 -> keys.jsonb_object_keys <> $2 -> keys.jsonb_object_keys then
jsonb_retorno = jsonb_retorno || format('{"%s": "old: %s, new: %s"}', keys.jsonb_object_keys, $1 ->> keys.jsonb_object_keys, $2 ->> keys.jsonb_object_keys)::jsonb;
end if;
end loop;
return jsonb_retorno;
end
$function$;
Finally the above function is added to the Trigger function in the section corresponding to the field diferenca
following the same logic but with some modifications for use of the type jsonb
.
CREATE OR REPLACE FUNCTION auditoria() RETURNS trigger AS $BODY$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
VALUES (TG_OP, now(), user, TG_TABLE_NAME, '{}'::jsonb, row_to_json(NEW.*), '{}'::jsonb);
END IF;
IF (TG_OP = 'UPDATE') THEN
INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD.*), row_to_json(NEW.*), fn_compara_jsonb(to_jsonb(row_to_json(OLD.*)), to_jsonb(row_to_json(NEW.*))));
END IF;
IF (TG_OP = 'DELETE') THEN
INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD.*), '{}'::jsonb, '{}'::jsonb);
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER audit AFTER INSERT OR UPDATE OR DELETE ON produto FOR EACH ROW EXECUTE PROCEDURE auditoria();
After updating the records with an SQL similar to: update produto set estoque=estoque+2;
, records would be generated in the table auditoria
as below:
stackoverflow=# select * from auditoria where operacao='UPDATE';
┌─[ RECORD 1 ]────────────────────────────────────────────────────────────┐
│ id │ 10 │
│ operacao │ UPDATE │
│ data │ 2018-10-01 21:18:14.928847-03 │
│ usuario │ postgres │
│ tabela │ produto │
│ antes │ {"codigo": 1, "estoque": 53, "descricao": "Filtro de óleo"} │
│ depois │ {"codigo": 1, "estoque": 55, "descricao": "Filtro de óleo"} │
│ diferenca │ {"estoque": "old: 53, new: 55"} │
├─[ RECORD 2 ]────────────────────────────────────────────────────────────┤
│ id │ 11 │
│ operacao │ UPDATE │
│ data │ 2018-10-01 21:18:14.928847-03 │
│ usuario │ postgres │
│ tabela │ produto │
│ antes │ {"codigo": 2, "estoque": 100, "descricao": "Cerveja 600ml"} │
│ depois │ {"codigo": 2, "estoque": 102, "descricao": "Cerveja 600ml"} │
│ diferenca │ {"estoque": "old: 100, new: 102"} │
└───────────┴─────────────────────────────────────────────────────────────┘
Tempo: 1,150 ms
The function fn_compara_jsonb()
can be evolved to according to the type of data return comparison information (greater than, smaller than, etc).
Another suggestion would be the table auditoria
have only one jsonb field named modificacoes
, for example, and in function auditoria()
treat how the data would be entered as per each operation (INSERT, UPDATE or DELETE).
I hope I’ve managed to give you a direction to solve your problem :)