Triggers och PL/pgSQL - Lär dig SQL

Triggers

Ibland finns det restriktioner vi vill sätta på våran databas som inte riktigt går med de restriktioner från tidigare kapitel. Vad vi kan göra då är att använda oss av triggers. En trigger är en block av kod som kan köras när en tabell modifieras på något sätt. Denna block av kod är dock inte skriven i SQL, utan det behövs skrivas i sitt egna minispråk. Vi kommer använda oss av standarden PL/pgSQL, som efterliknar SQL. Det finns dock andra språk, som exempelvis PL/python3u som efterliknar python.

Att sätta en trigger på en tabell är två processer:

Skriv en funktion som ska användas av triggern.
Skapa en trigger som kör funktionen.

Funktionen skapas genom syntaxen

CREATE FUNCTION <name>() RETURNS trigger AS $<token>$
    BEGIN
        <code>
    END;
$<token>$ LANGUAGE plpgsql;

<name> är namnet på funktionen.
<token> Allt mellan $<token>$ och $<token>$ är en sträng. Token är därav bara ett namn som talar om var strängen börjar och slutar. Detta kan vara vad som helst.
<code> är där vi skriver vår PL/pqSQL kod.

Själva triggern skapas genom syntaxen

CREATE TRIGGER <name> 
<before_after> <action> ON <table>
FOR EACH ROW
EXECUTE PROCEDURE <function_name>();

<name> är namnet på triggern. Kan vara bra för att identifiera vilken triggern som eventuellt hindrar dig från att göra något.
<before_after> är antingen BEFORE eller AFTER, beroende på om du vill att triggern ska köras innan eller efter du försökt göra ett action.
<action> kan vara INSERT, UPDATE eller DELETE, beroende på om du vill att triggern ska köras när något sätts in, uppdateras eller tas bort.
<table> är namnet på tabellen du vill att triggern ska ligga på.
<function_name> är namnet på funktionen du vill ska köras när trigger aktiveras.

För att ge ett konkret exempel kan vi lägga till en trigger som hindrar oss om vi försöker uppdatera emailen på en användare till något som redan används.

CREATE FUNCTION check_email() RETURNS trigger AS $string_token$
    BEGIN
        IF NEW.email IN (SELECT email FROM Users) THEN
            RAISE EXCEPTION 'Someone already has the email "%"', NEW.email;
        ELSE
            RETURN NEW;
        END IF;
    END;
$string_token$ LANGUAGE plpgsql;

CREATE TRIGGER check_email 
BEFORE UPDATE ON users
FOR EACH ROW 
EXECUTE PROCEDURE check_email();

För att kontrollera att den fungerar, skriv dessa kommandon:

UPDATE users
SET email = 'watercooler@hotmail.com'
WHERE user_id = 1;

UPDATE users
SET email = 'watercooler@hotmail.com'
WHERE user_id = 2;

Den nya syntax för PL/pgSQL

Även om PL/pgSQL liknar vanlig SQL så finns det några andra saker vi kan göra.

Vi kan definiera en traditionell if-sats som låter oss att köra olika kod beroende på ett eller flera villkor. Detta fungerar likt Python eller liknande programmeringsspråk. Syntaxen kräver dock att varje villkor avslutas med ett THEN innan koden som ska köras kommer. Dessutom måste varje block avslutas med ett END IF.

IF <condition> THEN
    <code>;
ELSIF <condition> THEN
    <code>;
ELSE
    <code>;
END IF;

Vi kan också kasta felmeddelanden för att meddela om något håller på att gå snett. I vårat exempel ovan skrev vi 'Someone already has the email' följt av mailets namn, ifall mailet redan var använt. Syntaxen är väldigt enkel:

RAISE EXCEPTION <message>;

En funktion kan också returnera värden. Alla triggers måste returnera ett värde. De bör antingen returner NEW, OLD eller NULL, men mer om detta nedan.

Utöver dessa kan du också använda dig av alla de SQL-kommandon som du lärt dig i kursen.

NEW och OLD

I funktioner för triggers kommer ett antal specialvariabler att finnas tillgängliga. De två viktigaste är NEW och OLD, som innehåller den nya tupeln och den gamla tupeln.

NEW : Finns för triggers på INSERT/UPDATE, men är NULL för DELETE.
OLD : Finns för triggers på UPDATE/DELETE, men är NULL för INSERT.

Som vi sa innan, en trigger måste alltid returnera någonting. Om vår trigger är definierad med AFTER så spelar det ingen roll vad som returneras; det kan lika gärna vara NULL. Om triggern är definierad med BEFORE gör det däremot skillnad.

ON DELETE : Returnera OLD för att radera, annars returnera NULL (eller kasta felmeddelande).
ON UPDATE : Returnera NEW för att uppdatera, annars returnera NULL eller OLD (eller kasta felmeddelande).
ON INSERT : Returnera NEW för att sätta in, annars returnera NULL (eller kasta felmeddelande).