Wat zijn triggers?

Een trigger is een in de database voorgeprogrammeerde routine die gekoppeld is aan een bepaalde bewerking op een tabel, zoals het uitvoeren van een INSERT of UPDATE query.

Trigger starten eigenlijk Stored_procedures op die in alles kunnen wat gewone stored procedures kunnen, plus nog wat extra. Dat extra bestaat in het feit dat je in een trigger toegang hebt tot het record dat wordt aangepast door de query die de trigger doet afgaan. Nog mooier, je hebt toegang tot dat record zoals het is voordat de query het verandert en tot zoals het zal zijn nadat de query het veranderd heeft.

MySQL ondersteunt triggers pas vanaf versie 5.0, PostgreSQL ondersteunt het in elke versie.

Triggers in MySQL

In het artikel Werken met MySQL gaat het over het beter werken met MySQL en met behulp van Foreign Keys zie je hoe bepaalde klusjes kunnen worden uitbesteed aan je database. Voor veel mensen moet dit aantrekkelijk klinken, want we zijn toch allemaal liever lui dan moe (ik in ieder geval wel :)).

Het voorbeeld dat daar genoemd werd ging over gebruikers en adressen, waarbij een foreign key voor ons het adres van een gebruiker weggooide als we de gebruiker verwijderde uit het systeem. Handig, maar kan het dan ook de andere kant op?

In dit artikel wil ik jullie laten zien dat ook het toevoegen van gegevens aan je database soms gemakkelijker kan dan meerdere queries uitvoeren, namelijk met Triggers.

Gebruikers en gebruikersgroepen

Voor het gemak pakken we weer dezelfde tabellen als in het artikel Werken met MySQL, aangevuld met een aantal nieuwe tabellen, om een praktische toepassing te demonstreren. Een systeem met gebruikers is natuurlijk een mooi begin, maar we hebben ook gebruikersgroepen nodig om onze gebruikers in te delen. We houden het even simpel, want het gaat om het idee.

# De tabellen uit het artikel "Werken met MySQL"
CREATE TABLE gebruikers (
  gebruiker_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  naam VARCHAR(20) NOT NULL,
  PRIMARY KEY (gebruiker_id)
) ENGINE=InnoDB;
 
CREATE TABLE adressen (
  adres_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  gebruiker_id INT UNSIGNED NOT NULL,
  plaats VARCHAR(40) NOT NULL,
  PRIMARY KEY (adres_id),
  KEY gebruiker_id (gebruiker_id),
  CONSTRAINT adressen_fk 
    FOREIGN KEY (gebruiker_id) 
    REFERENCES gebruikers (gebruiker_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB;
 
# De gebruikersgroepen
CREATE TABLE groepen (
  groep_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  groep_naam VARCHAR(30) NOT NULL,
  PRIMARY KEY (groep_id),
  UNIQUE KEY groep_naam (groep_naam)
) ENGINE=InnoDB;
 
# De tabel waarin we de gebruikers en de groepen koppelen
CREATE TABLE gebruiker_heeft_groepen (
  groep_id INT UNSIGNED NOT NULL,
  gebruiker_id INT UNSIGNED NOT NULL,
  UNIQUE KEY gebruiker_groep_combi (groep_id,gebruiker_id),
  KEY k_groep_id (groep_id),
  KEY k_gebruiker_id (gebruiker_id),
  CONSTRAINT gebruiker_heeft_groepen_fk1 
    FOREIGN KEY (gebruiker_id) 
    REFERENCES gebruikers (gebruiker_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE,
  CONSTRAINT gebruiker_heeft_groepen_fk 
    FOREIGN KEY (groep_id) 
    REFERENCES groepen (groep_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB;

Een trigger maken

Nu we gebruikersgroepen hebben, gaan we er meteen maar een maken voor de standaard gebruikers van onze applicatie. We noemen hem dan ook Standaard gebruikers.

INSERT INTO groepen (groep_naam) VALUES ('Standaard gebruikers');

Iedereen die als gebruiker in het systeem komt zal onderdeel uitmaken van deze groep. Gebruikers kunnen natuurlijk in meerdere groepen zitten, maar dat laat ik even buiten beschouwing. Nu kunnen we natuurlijk 2 queries gaan gebruiken als een nieuwe gebruiker aangemeld wordt, maar dit gaan we niet doen: we gaan een trigger gebruiken om automatisch een nieuwe gebruiker aan de groep Standaard gebruikers toe te voegen.

CREATE TRIGGER gebruikers_after_ins_tr 
  AFTER INSERT ON gebruikers
  FOR EACH ROW
BEGIN
INSERT INTO gebruiker_heeft_groepen
  (groep_id, gebruiker_id)
VALUES
  (1, NEW.gebruiker_id);
END;

Als je het zo ziet staan is het helemaal niet zo ingewikkeld, maar voor de duidelijkheid staat er dus dat deze trigger af moet gaan nadat er een nieuwe rij is ingevoerd. Wanneer we dat dus doen

INSERT INTO gebruikers (naam) VALUES ('Piet');

zal voor elke nieuwe rij er ook een nieuwe rij toegevoegd worden aan de tabel gebruiker_heeft_groepen waarbij 1 wordt gebruikt als de groep (Standaard gebruikers) en het insert_id van de nieuwe gebruiker als gebruiker_id.

mysql> SELECT * FROM gebruiker_heeft_groepen;
+----------+--------------+
| groep_id | gebruiker_id |
+----------+--------------+
|        1 |            1 |
+----------+--------------+

Triggers in PostgreSQL

Voor het gemak gebruik ik de tabellen van het MySQL voorbeeld. Noot: de prefix 'public.' staat in PostgreSQL voor een schema, niet voor een databasenaam. Je mag het ook weglaten, dan zal PostgreSQL de tabellen maken in het standaard schema (en dat is ook 'public' als je het niet zelf verandert)

CREATE TABLE public.gebruikers (
  gebruiker_id SERIAL, 
  naam VARCHAR(20) NOT NULL, 
  CONSTRAINT gebruikers_pkey PRIMARY KEY(gebruiker_id)
) WITHOUT OIDS;
 
 
CREATE TABLE public.adressen (
  adres_id SERIAL, 
  gebruiker_id INTEGER NOT NULL, 
  plaats VARCHAR(40) NOT NULL, 
  CONSTRAINT adressen_pkey PRIMARY KEY(adres_id), 
  CONSTRAINT adressen_fk FOREIGN KEY (gebruiker_id)
    REFERENCES public.gebruikers(gebruiker_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;
 
 
CREATE TABLE public.groepen (
  groep_id SERIAL, 
  groep_naam VARCHAR(30) NOT NULL, 
  CONSTRAINT groepen_groep_naam_key UNIQUE(groep_naam), 
  CONSTRAINT groepen_pkey PRIMARY KEY(groep_id)
) WITHOUT OIDS;
 
 
CREATE TABLE public.gebruiker_heeft_groepen (
  groep_id SERIAL, 
  gebruiker_id INTEGER, 
  CONSTRAINT gebruiker_heeft_groepen_fk FOREIGN KEY (groep_id)
    REFERENCES public.groepen(groep_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE, 
  CONSTRAINT gebruiker_heeft_groepen_fk1 FOREIGN KEY (gebruiker_id)
    REFERENCES public.gebruikers(gebruiker_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
    NOT DEFERRABLE
) WITHOUT OIDS;

Het invoeren van de groep standaard gebruikers gaat exact zoals in MySQL, omdat dat standaard SQL is:

INSERT INTO groepen (groep_naam) VALUES ('Standaard gebruikers');

De trigger

Omdat de scheiding tussen trigger en achterliggende functie in PostgreSQL zeer duidelijk is zie je dat ook terug in de create statements.

Eerst de functie die zal worden aangeroepen in de trigger. Let op dat deze functie een returntype TRIGGER heeft, dat maakt hem bruikbaar in een trigger.

CREATE OR REPLACE FUNCTION public.trigfunc_plaatsgebruikeringroep () RETURNS TRIGGER AS
$body$
DECLARE
 
BEGIN
INSERT INTO gebruiker_heeft_groepen
  (groep_id, gebruiker_id)
VALUES
  (1, NEW.gebruiker_id);  
  RETURN NEW;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

En dan de trigger zelf. Dit statement bepaalt wanneer de trigger af moet gaan en welke functie er aangeroepen moet worden.

CREATE TRIGGER trig_plaatsgebruikeringroep AFTER INSERT 
ON public.gebruikers FOR EACH ROW 
EXECUTE PROCEDURE public.trigfunc_plaatsgebruikeringroep();

Triggers aan- en uitzetten

Deze trigger zal altijd, voor elke user die wordt ingevoerd een groepsrecord maken. Soms moet je echter bewerkingen doen waarbij je niet wilt dat deze trigger afgaat, denk aan het terugzetten van een backup; een backup zal ook de tabel gebruiker_heeft_groepen herstellen dus dan heeft het geen zin om die gegevens door de trigger te laten maken. Voor die gevallen kun je in PostgreSQL de trigger eenvoudig aan- of uitzetten met een ALTER TABLE query:

Uitzetten:

ALTER TABLE public.gebruiker_heeft_groepen
DISABLE TRIGGER trig_plaatsgebruikeringroep;

Aanzetten:

ALTER TABLE public.gebruiker_heeft_groepen
ENABLE TRIGGER trig_plaatsgebruikeringroep;

Wanneer je binnen een transaction een trigger uitzet (PostgreSQL staat dit toe), zorgt dit voor een table lock. Niemand anders kan dan met de tabel werken, er is dus niemand anders die per ongeluk voor corrupte data kan zorgen.