Triggers

• Triggere i databasen aktiverer funktioner i forbindelse med begivenheder på databasen. I det følgende gennemgår vi tabel-triggere som aktiveres ved INSERT, UPDATE og DELETE statements

• Principielt findes tre typer triggere:

  • Before
  • After
  • Instead of

Before

Before bruges typisk når "noget" skal laves om på de data, der ændres / sættes ind eller noget skal tilføjes.

Eksempel: Beregn arealet af en geometri ved hver INSERT/UPDATE

ALTER TABLE att0902.bygninger ADD COLUMN area double precision;

CREATE OR REPLACE FUNCTION calc_area()
RETURNS trigger AS
$BODY$
BEGIN
  NEW.area := st_area(NEW.geom);
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER bygninger_area_trig BEFORE INSERT OR UPDATE ON att0902.bygninger
  FOR EACH ROW EXECUTE PROCEDURE calc_area();

SELECT * FROM att0902.bygninger ;

INSERT INTO att0902.bygninger(
            geom, "Info", "Objekttype", "Z", "Konverteret")
    VALUES (ST_GeomFromText('POLYGON((390309.70572787 7538535.16303447,390306.811436522 7538533.90329089,390307.759708616 7538531.72373453,390304.79120467 7538530.44399502,390308.015329789 7538522.94552131,390322.478540685 7538529.21424533,390319.097744523 7538536.62273735,390310.637508276 7538532.97348015,390309.70572787 7538535.16303447))',32622),
     '--', 'SKUR', 66.66, '20110110');

Trigger der opdaterer et materialized view når underliggende tabeller opdateres

CREATE OR REPLACE FUNCTION trig_refresh_lokationer_mv()
  RETURNS trigger AS
$BODY$
BEGIN

    REFRESH MATERIALIZED VIEW trap.lokationer_mv;
    REFRESH MATERIALIZED VIEW trap.lokationer_mv_point;
    RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION trig_refresh_lokationer_mv()
  OWNER TO trap;


CREATE TRIGGER trig_01_refresh_lokationer_mv AFTER TRUNCATE OR INSERT OR UPDATE OR DELETE
   ON trap.egne_punkter  FOR EACH STATEMENT
   EXECUTE PROCEDURE trig_refresh_lokationer_mv();

Trigger der opdaterer en flades arbejdssted (udfra en anden polygon). Håndterer også hvis flere flader overlappes

DROP TRIGGER IF EXISTS gartner_flade_arbejdssted ON gartner.flade;
CREATE TRIGGER gartner_flade_arbejdssted
  BEFORE INSERT OR UPDATE
  ON gartner.flade
  FOR EACH ROW
  EXECUTE PROCEDURE gartner.flade_arbejdssted_trg();

CREATE OR REPLACE FUNCTION gartner.flade_arbejdssted_trg()
  RETURNS trigger AS
$BODY$
  BEGIN
    IF (TG_OP = 'UPDATE') THEN
      -- Insert punkt
      IF NEW.arbejdssted is NULL THEN
      NEW.arbejdssted =  (SELECT
          a.arbejdssted_id
          FROM  gartner.arbejdssted a
            WHERE st_intersects(NEW.geometry,a.geometry)
            ORDER BY st_Area(st_intersection(a.geometry,NEW.geometry)) DESC LIMIT 1);

      RETURN NEW;
      END IF;
      RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
       IF NEW.arbejdssted is NULL THEN
        NEW.arbejdssted =  (SELECT
          a.arbejdssted_id
          FROM  gartner.arbejdssted a
            WHERE st_intersects(NEW.geometry,a.geometry)
            ORDER BY st_Area(st_intersection(a.geometry,NEW.geometry)) DESC LIMIT 1);
      RETURN NEW;
        END IF;
      RETURN NEW;
    END IF;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Trigger der automatisk opdaterer en kolonne med timestamp

ALTER TABLE raw_data.parkarealer_region
  ADD COLUMN senest_opdateret TIMESTAMP;

CREATE OR REPLACE FUNCTION update_senest_opdateret_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.senest_opdateret = now();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_park_modtime
 BEFORE UPDATE ON  raw_data.parkarealer_region
  FOR EACH ROW EXECUTE PROCEDURE  update_senest_opdateret_column();

• Eksempler:
  • Etabler centroide punkt når geometrien ændres indsættes
  • Implementer forretningsregler (f.eks. "Arealet af en polygon må ikke være større end det definerede maksimum for objekttypen (defineret i en anden tabel)

After

Typisk når "noget" skal tilføjes i forbindelse med en operation

• Eksempel:
  • Vedligehold en "log" tabel over alle de operationer, der foretages

Instead of

• Eksempler:
  • I stedet for at indsætte en række i et view, indsæt i de underliggende tabeller
  • "Rejs" en fejl når det er forbudt at slette i en tabel (også for administrator)

Øvelse

Tilføj et arealfelt til bygningstabellen (integer). Byg en trigger til tabellen, der automatisk opdaterer feltet når man tegner en ny bygning.

Brug gerne den funktion du byggede i funktions-øvelsen.

Extra credit, hvis du har masser af tid

Modificer din afrundingsfunktion til at tage en ekstra parameter, p. Hvis p. er 1 fungerer den som før, dvs. til nærmeste heltalt. Hvis p er 5 bliver der afrundet til nærmeste 5'er (dvs. 49 bliver til 50 og 83 bliver til 85).

Opdater triggerfunktionen til at afrunde til nærmeste heltal på små bygninger, nærmeste 5'er på mellemstore bygninger og nærmeste 10'er på store bygninger.