Kom godt i gang med SQL

Anvend betingelser og triggere til forretningsregler

I erhvervslivet er der visse regler, der altid skal overholdes. En medarbejder, der arbejder på et projekt, skal f.eks. stå på lønningslisten. Der kan også være bestemte aktiviteter, der skal udføres automatisk. Når en sælger f.eks. får en ordre i hus, skal hans eller hendes provision stige.

DB2 Universal Database har en række nyttige funktioner til dette formål:

Det første afsnit indeholder en oversigt over nøgler. Senere gennemgås referenceintegritet, betingelser og triggere vha. eksempler og diagrammer.

Nøgler

En nøgle er et sæt kolonner, som kan bruges til at identificere eller få adgang til en bestemt række eller bestemte rækker.

En nøgle, der består af mere end én kolonne, kaldes en sammensat nøgle. I en tabel med en sammensat nøgle svarer rækkefølgen af kolonnerne inden for den sammensatte nøgle ikke nødvendigvis til deres rækkefølge i tabellen.

Entydige nøgler

En entydig nøgle er defineret som en eller flere kolonner, hvor hver kolonne ikke må indeholde to ens værdier. Kolonnerne i en entydig nøgle må ikke indeholde NULL-værdier. Når INSERT- og UPDATE-sætninger udføres, kontrollerer databasesystemet, at betingelsen overholdes. En tabel kan have flere entydige nøgler. Entydige nøgler er valgfri og kan defineres i CREATE TABLE- eller ALTER TABLE-sætninger.

Primærnøgler

En primærnøgle er en entydig nøgle, som er en del af tabeldefinitionen. En tabel kan kun have én primærnøgle, og primærnøglen må ikke indeholde NULL-værdier. Primærnøgler er valgfri og kan defineres i CREATE TABLE- eller ALTER TABLE-sætninger.

Fremmednøgler

En fremmednøgle angives i definitionen af en referencebetingelse. En tabel kan have nul eller flere fremmednøgler. Værdien for den sammensatte fremmednøgle er NULL, hvis en komponent i værdien er NULL. Fremmednøgler er valgfri og kan defineres i CREATE TABLE- eller ALTER TABLE-sætninger.

Entydige betingelser

En entydig betingelse sørger for, at værdierne for en nøgle en entydige inden for en tabel. Entydige betingelser er valgfri. Du kan definere dem vha. sætningen CREATE TABLE eller ALTER TABLE ved at angive udtrykket PRIMARY KEY eller UNIQUE. For eksempel kan du definere en entydig betingelse for en tabelkolonne, der indeholder personalenummer, så alle medarbejdere får et entydigt nummer.

Referenceintegritetsbetingelser

Vha. entydige betingelser og fremmednøgler kan du angive koblinger mellem tabeller og dermed sørge for, at visse forretningsregler overholdes. Kombinationen entydig nøgle og fremmednøglebetingelser kaldes ofte referenceintegritetsbetingelser. En entydig betingelse, som en fremmednøgle henviser til, kaldes en overordnet nøgle. En fremmednøgle henviser til eller er beslægtet med en bestemt overordnet nøgle. Der kan f.eks. være en regel, som siger, at alle medarbejdere (tabellen EMPLOYEE), skal tilhøre en eksisterende afdeling (tabellen DEPARTMENT). Derfor defineres afdelingsnummeret i tabellen EMPLOYEE som fremmednøgle, og afdelingsnummeret i tabellen DEPARTMENT som primærnøgle. Diagrammet nedenfor illustrerer, hvordan en referenceintegritetsbetingelse fungerer.

Fig. 4. Fremmed- og primærnøglebetingelser definerer koblinger og beskytter data


REQTEXT

Tabelkontrolbetingelser

En tabelkontrolbetingelse angiver en betingelse, som vurderes for hver række i en tabel. Du kan angive kontrolbetingelser for enkeltkolonner. Tilføj dem vha. sætningen CREATE TABLE eller ALTER TABLE.

Nedenfor oprettes en tabel med følgende betingelser:

     CREATE TABLE EMP
           (ID           SMALLINT NOT NULL,
            NAME         VARCHAR(9),
            DEPT         SMALLINT CHECK (DEPT BETWEEN 10 AND 100),
            JOB          CHAR(5)   CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')),
            HIREDATE     DATE,
            SALARY       DECIMAL(7,2),
            COMM         DECIMAL(7,2),
            PRIMARY KEY (ID),
            CONSTRAINT YEARSAL CHECK
                               (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )

En betingelse overholdes, medmindre den er falsk. Hvis f.eks. DEPT er NULL for en indsat række, indsættes den uden fejl, selv om værdien for DEPT skal ligge mellem 10 og 100, som angivet i betingelsen.

I dette eksempel får tabellen EMPLOYEE tilføjet betingelsen COMP, som angiver at en medarbejders samlede aflønning skal overstige 15.000:

     ALTER TABLE EMP
        ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)

De eksisterende rækker i tabellen kontrolleres for at sikre, at de ikke er i strid med den nye betingelse. Du kan udskyde kontrollen ved at bruge SET CONSTRAINTS-sætningen sådan:

     SET CONSTRAINTS FOR EMP OFF
     ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
     SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
 

Først bruges SET CONSTRAINTS-sætningen til at udskyde betingelseskontrol for tabellen. Derefter kan én eller flere betingelser tilføjes, uden at tabellen kontrolleres. Derefter afsendes SET CONSTRAINTS-sætningen igen, så betingelseskontrol aktiveres, og udskudt kontrol foretages.

Triggere

En trigger definerer et sæt handlinger. Triggere aktiveres af funktioner, der ændrer data i en angivet basistabel.

Triggere kan f.eks. anvendes til:

Med triggere bliver applikationsudvikling hurtigere, forretningsregler kan anvendes globalt, og det bliver lettere at vedligeholde applikationer og data.

DB2 Universal Database giver dig mulighed for at benytte flere typer triggere. Triggere kan defineres til at blive aktiveret før eller efter en DELETE-, INSERT- eller UPDATE-funktion. Hver trigger indeholder en række SQL-sætninger, som kaldes en udløst handling. Den kan bl.a. omfatte et valgfrit søgekriterium.

En efter-trigger (AFTER)kan defineres, så den udløste handling udføres for hver række, eller én gang for sætningen. Før-triggere (BEFORE) udfører altid den udløste handling for hver række.

Brug en trigger før en INSERT-, UPDATE- eller DELETE-sætning for at kontrollere visse betingelser, før en triggerhandling udføres, eller for at ændre inputværdierne, inden de gemmes i tabellen.

Brug en AFTER-trigger til at sprede værdier efter behov eller til at udføre andre funktioner, f.eks. afsendelse af en meddelelse, som kræves i forbindelse med triggerhandlingen.

I dette eksempel illustreres brugen af BEFORE- og AFTER-triggere. En applikation registrerer og sporer ændringer i aktiekurser. Databasen indeholder to tabeller, CURRENTQUOTE og QUOTEHISTORY, defineret som:

     CREATE TABLE CURRENTQUOTE
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      STATUS VARCHAR(9))
 
     CREATE TABLE  QUOTEHISTORY
     (SYMBOL VARCHAR(10),
      QUOTE DECIMAL(5,2),
      TIMESTAMP TIMESTAMP)

Når kolonnen QUOTE i CURRENTQUOTE opdateres vha. en sætning som:

     UPDATE CURRENTQUOTE
        SET QUOTE = 68.5
        WHERE SYMBOL = 'IBM'

skal kolonnen STATUS i CURRENTQUOTE opdateres med angivelse af, om aktiekursen er:

Det gøres vha. følgende BEFORE-trigger:

(1)

     CREATE TRIGGER STOCK_STATUS
        NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE
        REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
        FOR EACH ROW MODE DB2SQL

(2)

      SET NEWQUOTE.STATUS =

(3)

         CASE

(4)

            WHEN NEWQUOTE.QUOTE >=
                      (SELECT MAX(QUOTE)
                          FROM QUOTEHISTORY
                          WHERE SYMBOL = NEWQUOTE.SYMBOL
                            AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
            THEN 'High'

(5)

          WHEN NEWQUOTE.QUOTE <=
                      (SELECT MIN(QUOTE)
                          FROM QUOTEHISTORY
                          WHERE SYMBOL = NEWQUOTE.SYMBOL
                          AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) )
          THEN 'Low'

(6)

          WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE
             THEN 'Rising'
          WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE
             THEN 'Dropping'
          WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE
             THEN 'Steady'
       END
 

(1)
Med denne kodeblok defineres STOCK_STATUS som en trigger, der skal aktiveres, før kolonnen QUOTE i tabellen CURRENTQUOTE opdateres. I anden linie angives det, at den udløste handling skal aktiveres før evt. ændringer, som den egentlige opdatering af tabellen CURRENTQUOTE medfører, aktiveres for databasen. Udtrykket NO CASCADE betyder, at den udløste handling ikke aktiverer andre triggere. I tredje linie angives de navne, som skal bruges som kvalifikatorer for kolonnenavnet for de nye værdier (NEWQUOTE) og de gamle værdier (OLDQUOTE). Kolonnenavne, der kvalificeres med disse korrelationsnavne (NEWQUOTE og OLDQUOTE), kaldes overgangsvariabler. Den fjerde linie angiver, at den udløste handling skal udføres for hver række.

(2)
Dette angiver begyndelsen på den første og eneste SQL-sætning i den udløste handling for triggeren. Sætningen SET overgangsvariabel bruges i en trigger til at knytte en værdi til en kolonne i rækken i den tabel, som opdateres vha. den sætning, som har aktiveret triggeren. Denne sætning knytter en værdi til kolonnen STATUS i tabellen CURRENTQUOTE.

(3)
Udtrykket til højre for tildelingen er et CASE-udtryk. CASE-udtrykket slutter ved nøgleordet END.

(4)
Først undersøges det, om den nye kurs (NEWQUOTE.QUOTE) er højere end eller lig med den højeste værdi for aktiesymbolet i indeværende kalenderår. Underforespørgslen bruger tabellen QUOTEHISTORY, som opdateres af den efterfølgende AFTER-trigger.

(5)
Først undersøges det, om den nye kurs (NEWQUOTE.QUOTE) er lavere end eller lig med den laveste værdi for aktiesymbolet i indeværende kalenderår. Underforespørgslen bruger tabellen QUOTEHISTORY, som opdateres af den efterfølgende AFTER-trigger.

(6)
I de sidste tre tilfælde sammenlignes den nye kurs (NEWQUOTE.QUOTE) med den hidtidige kurs (OLDQUOTE.QUOTE) for at afgøre, om den er højere, lavere eller uændret. Her slutter sætningen SET overgangsvariabel.

Når registreringen i tabellen CURRENTQUOTE opdateres, skal der også oprettes en kontrolrecord, ved at den nye kurs og dens tidsstempel kopieres til tabellen QUOTEHISTORY. Det gøres med følgende AFTER-trigger:

(1)

     CREATE TRIGGER RECORD_HISTORY
     AFTER UPDATE OF QUOTE ON CURRENTQUOTE
     REFERENCING NEW AS NEWQUOTE
     FOR EACH ROW MODE DB2SQL
     BEGIN ATOMIC

(2)

     INSERT INTO QUOTEHISTORY
        VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP);
     END

(1)
Med denne kodeblok defineres en trigger, RECORD_HISTORY, der skal aktiveres, efter kolonnen QUOTE i tabellen CURRENTQUOTE opdateres. I tredje linie angives det navn, som skal bruges som kvalifikator for kolonnenavnet for den nye værdi (NEWQUOTE). Den fjerde linie angiver, at den udløste handling skal udføres for hver række.

(2)
Den udløste handling for denne trigger indeholder en enkelt SQL-sætning, som indsætter en række i tabellen QUOTEHISTORY vha. data fra den række, der er blevet opdateret (NEWQUOTE.SYMBOL og NEWQUOTE.QUOTE) og det aktuelle tidsstempel.

CURRENT TIMESTAMP er et specialregister, der indeholder tidsstemplet. Du kan finde en oversigt og forklaring under Specialregistre.


[ Øverst på siden | Forrige side | Næste side | Indholdsfortegnelse | Stikordsregister ]