Forums PostgreSQL.fr

Juju

Membre

Trigger d'audit : déterminer les colonnes qui ont été modifiées

Bonjour à tous,

Petite prise de tête du vendredi
Je suis en Postgres v11.5 sous CentOS.

Je veux écrire un trigger qui lors d'un update sur une table stockerait dans une autre table (dans un jsonb) seulement les valeurs réellement modifiées.
J'ai pour cela créé un trigger (for each statement) et ma fonction trigger. J'essaye de trouver la méthode la moins lourde à mettre en place (sachant que potentiellement je vais devoir faire cet audit sur pas mal d'autres tables). Pour l'instant, à part comparer les colonnes 1 à 1 entre anciennes et nouvelles lignes je ne vois rien de plus rapide à écrire. L'idéal serait une façon plus générique ou dynamique de faire ça mais je bloque un peu là. Si quelqu'un a une idée je suis preneur ! Pour info la table contient des colonnes de tous types (integer, varchar, date, double , geometry). Pour les colonnes nullable je pourrais utiliser COALESCE mais le problème c'est que je ne maitrise pas forcément ce qui peut être rentrée dans certaines colonnes donc ça me semblait plus sûr avec cette méthode.

CREATE OR REPLACE FUNCTION MySchema.MyFunction() RETURNS TRIGGER AS $BODY$
DECLARE
	oldRow RECORD;
	newRow RECORD;
BEGIN
	
	-- For each row, we will compare each attribute and store in the event only ones that have changed
	FOR oldRow IN SELECT * FROM old_rows
	LOOP
		SELECT * INTO newRow FROM new_rows WHERE id = oldRow.id;
		
		-- For not null columns
		IF oldRow.col1 <> newRow.col1 THEN
			raise notice 'col1 changed';
		END IF;
		
		-- For null columns
		IF oldRow.col2 <> newRow.col2
			OR oldRow.col2 is null and newRow.col2 is not null
			OR oldRow.col2 is not null and newRow.col2 is null THEN
			raise notice 'col2 changed';
		END IF;
		
		-- Etc...
	
	END LOOP;
	
	RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql;

CREATE TRIGGER MyTrigger
    AFTER UPDATE ON MyTable
    REFERENCING OLD TABLE AS old_rows NEW TABLE AS new_rows
    FOR EACH STATEMENT EXECUTE FUNCTION MySchema.MyFunction();

Merci d'avance !
Julien.

gleu

Administrateur

Re : Trigger d'audit : déterminer les colonnes qui ont été modifiées

La méthode habituelle revient plutôt à utiliser IS DISTINCT FROM pour gérer NULL et non NULL.

Je dirais aussi malicieusement que je ne vois pas bien l'intérêt de coder son propre outil alors que ça existe déjà. Une recherche rapide remonte E-maj (https://pgxn.org/dist/e-maj/) et table_version (https://pgxn.org/dist/table_version/).

---

Guillaume.

Juju

Membre

Re : Trigger d'audit : déterminer les colonnes qui ont été modifiées

Bonjour,
Merci pour votre réponse, je ne connaissais pas le IS DISTINCT FROM (qui rend l'écriture de la condition moins lourde !!).
Je vais regarder du côté de ces extensions, j'avais entendu parler de E-maj mais pas de l'autre, je dois formater les données d'une façon bien précise donc je ne sais pas si ça répondra à mon besoin.
Merci et bonne journée.
Julien.