Historik med triggers

At opbygge en historik kræver at en tabel som minumum indeholder oplysninger om hver rækkes gyldighedsperiode.

Opret en eksempeltabel

Det er nyttigt hvis man ikke vil eksperimentere på sine egne data.

CREATE TABLE test_pkt (
  fid SERIAL,
  geom geometry(Point, 25832),
  navn CHARACTER VARYING(32),
  CONSTRAINT test_pkt_pkey PRIMARY KEY (fid)
);

INSERT INTO test_pkt(geom, navn) VALUES
(ST_GeomFromText('POINT(0 0)',25832), 'Punkt 1'),
(ST_GeomFromText('POINT(0 1)',25832), 'Punkt 2'),
(ST_GeomFromText('POINT(0 2)',25832), 'Punkt 3'),
(ST_GeomFromText('POINT(0 3)',25832), 'Punkt 4'),
(ST_GeomFromText('POINT(0 4)',25832), 'Punkt 5'),
(ST_GeomFromText('POINT(0 5)',25832), 'Punkt 6'),
(ST_GeomFromText('POINT(0 6)',25832), 'Punkt 7');

Klargøring

Vi har nu en tabel, der i princippet kunne være hvilken som helst slags data. Nu skal den gøres klar til historik

Tilføj ekstra felter og sæt alle nuværende objekter som gyldige

ALTER TABLE test_pkt
ADD COLUMN version_id uuid DEFAULT uuid_generate_v4() NOT NULL,
ADD COLUMN registrering_fra TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
ADD COLUMN registrering_til TIMESTAMP DEFAULT NULL,
ADD COLUMN handling character varying(100) DEFAULT NULL,
ADD COLUMN bruger character varying(100) DEFAULT NULL
;

UPDATE test_pkt SET gyldig_fra = '1900-01-01 00:00:00', handling = 'Indlæst';

Ændr tabellens primære nøgle

ALTER TABLE test_pkt DROP CONSTRAINT test_pkt_pkey;
ALTER TABLE test_pkt ADD PRIMARY KEY (fid, version_id);

Lav et view, der filtrerer således at kun nuværende objekter vises

CREATE VIEW test_pkt_fv AS
SELECT fid, geom, navn,
       version_id
FROM test_pkt
WHERE gyldig_fra <= CURRENT_TIMESTAMP AND (gyldig_til IS NULL OR gyldig_til >= CURRENT_TIMESTAMP);

Opsætning af trigger

Opdateringsfunktion

For at kunne lave en trigger skal den første have en funktion, altså det noget der sker når noget andet sker. Det er det her der er det "svære".

Nedenstående håndterer kun slette-operationer.

CREATE OR REPLACE FUNCTION test_pkt_hist_trg()
  RETURNS trigger AS
$BODY$
  BEGIN
    IF (TG_OP = 'DELETE') THEN
        -- Return "0 rows deleted" IF NOT EXISTS

        IF NOT EXISTS
           (SELECT '1' FROM test_pkt
                WHERE fid = OLD.fid  AND version_id=OLD.version_id
                    AND (registrering_til IS NULL
                        OR gyldig_registrering >= CURRENT_TIMESTAMP))
            )
            THEN
                RETURN NULL;
        END IF;

        UPDATE test_pkt
            SET gyldig_til = current_timestamp,
                handling = 'Slettet',
                bruger = current_user
            WHERE version_id=OLD.version_id;

       RETURN OLD;
    END IF;
  END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

ALTER FUNCTION test_pkt_hist_trg()
  OWNER TO postgres;

Selve triggeren

Selve triggeren sættes op til at vores funktion kører når (forstået som I STEDET FOR) vores view bliver udsat for INSERT, UPDATE eller DELETE.

Triggeren håndterer det rækkevis.

CREATE TRIGGER test_pkt_hist_trg_iud INSTEAD OF INSERT OR UPDATE OR DELETE
   ON public.test_pkt_fv FOR EACH ROW
   EXECUTE PROCEDURE public.test_pkt_hist_trg();

Historik