Tracer et suivre les modifications apportées aux données d'une table

Tiré de http://www.postgresonline.com/journal/archives/87-PostgreSQL-8.3-PLPGSQL-Cheatsheet-Overview.html

Savoir qui fait quoi et quand sur quelle donnée d'une table, garder l'historique de ces modifications

• une table “ma_table” avec 4 attributs id_data, attr_1, attr_2 et attr_3

CREATE TABLE ma_table
(
  id_data serial NOT NULL PRIMARY KEY,
  attr_1 character varying,
  attr_2 character varying,
  attr_3 character varying
);

• elle contiendra les même colonnes que la table à suivre
• plus 3 attributs qui stockeront:
  • le nom du rôle qui effectue l'opération
  • le type d'opération : insert, update ou delete
  • la date et l'heure de l'opération

CREATE TABLE suivi_ma_table
(
  operation character(6),
  date_operation timestamp without time zone NOT NULL,
  utilisateur character varying NOT NULL,
  id_data integer,
  attr_1 character varying,
  attr_2 character varying,
  attr_3 character varying
);

CREATE OR REPLACE FUNCTION alimente_suivi_ma_table()
  RETURNS TRIGGER AS
$BODY$
    BEGIN
        --
        -- Crée une ligne dans la table suivi qui reflète l'opération effectuées sur la table "ma_table",
        -- utilise la variable spéciale TG_OP qui décrit l'opération courante
        --
        IF (TG_OP = 'DELETE') THEN
        -- en cas de suppression on stocke la ligne supprimée
            INSERT INTO suivi_ma_table SELECT 'DELETE', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
        -- en cas de mise à jour on stocke la ligne dans son état "après mise à jour" 
            INSERT INTO suivi_ma_table 'UPDATE', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
        -- en cas d'insertion on stocke la ligne insérée
            INSERT INTO suivi_ma_table SELECT 'INSERT', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; 
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER suivi_saisie_observation
  AFTER INSERT OR UPDATE OR DELETE
  ON ma_table
  FOR EACH ROW
  EXECUTE PROCEDURE alimente_suivi_ma_table();

le rôle de connexion “rss” aura les droit de select sur la table “ma_table”

<?php
header("Content-Type: application/xml; charset=ISO-8859-1");
$dbcon=pg_connect("host=192.168.1.210 dbname=test user=rss password=rss");
 
if (isset($_GET['operation'])&&$_GET['operation']!='') {$where = 'lower(operation)=\''. $_GET["operation"] .'\'';}
 
$query = '
  SELECT operation, date_operation, utilisateur, id_data, attr_1, attr_2, attr_3
  FROM suivi_ma_table
  ORDER BY date_operation DESC
  LIMIT 20;
';
//echo $query;
$item = '<?xml version="1.0" encoding="ISO-8859-1" ?>
<rss version="2.0">
<channel>
		<title>Ma table - dernières modificationa appoertées</title>
		<link>http://www.google.fr</link>
		<description>Suivi de modifications apportées à la table ma_table</description>
		<language>Français</language>';
$result = pg_query($dbcon,$query);
while($row = pg_fetch_array($result))
{
			$item.= '
<item>
	<title>'. utf8_decode($row["nom_complet"]) .'</title>
	<link>http://url_a_definir.org'</link>
	<description><![CDATA['.utf8_decode($row["operation"]) .' par '. utf8_decode($row["utilisateur"]) . ' - <em>donnée n°' . utf8_decode($row["id_data"]) . '</em>]]></description>
</item>';
}
$item.='</channel></rss>';
echo $item;
?>