Rules, funksjoner og triggere

Eksempel-tabeller

matRetter

CREATE TABLE matRetter(
  rettid int,
  navn varchar(25),
  PRIMARY KEY(rettid)
);

INSERT INTO matRetter VALUES(1, 'kjøttkaker');
INSERT INTO matRetter VALUES(2, 'pizza');
INSERT INTO matRetter VALUES(3, 'kyllingsalat');
INSERT INTO matRetter VALUES(4, 'laksesnitzel');
INSERT INTO matRetter VALUES(5, 'lasagne');
INSERT INTO matRetter VALUES(6, 'taco');
INSERT INTO matRetter VALUES(7, 'oksestek');
INSERT INTO matRetter VALUES(8, 'wienersnitzel');
INSERT INTO matRetter VALUES(9, 'pastasalat');
INSERT INTO matRetter VALUES(10, 'hummergryte');
INSERT INTO matRetter VALUES(11, 'fylt torsk');
INSERT INTO matRetter VALUES(12, 'reker');
INSERT INTO matRetter VALUES(13, 'fylt paprika');
INSERT INTO matRetter VALUES(14, 'ribbe');
INSERT INTO matRetter values(15, 'tomatsuppe');
INSERT INTO matRetter values(16, 'hamburger');
INSERT INTO matRetter values(17, 'lammegryte');
INSERT INTO matRetter values(18, 'tapas');
INSERT INTO matRetter values(19, 'ertesuppe');
INSERT INTO matRetter values(20, 'koteletter');

ukeDager

CREATE TABLE ukeDager(
  dagnr int,
  dagnavn varchar(7),
  PRIMARY KEY(dagnr)
);

INSERT INTO ukeDager VALUES(1, 'mandag');
INSERT INTO ukeDager VALUES(2, 'tirsdag');
INSERT INTO ukeDager VALUES(3, 'onsdag');
INSERT INTO ukeDager VALUES(4, 'torsdag');
INSERT INTO ukeDager VALUES(5, 'fredag');
INSERT INTO ukeDager VALUES(6, 'lørdag');
INSERT INTO ukeDager VALUES(7, 'søndag');

middagsListe

CREATE TABLE middagsListe(
  ukenr int,
  ukedag int,
  rett int,
  PRIMARY KEY (ukenr, ukedag),
  FOREIGN KEY (ukedag) references ukeDager,
  FOREIGN KEY (rett) references matRetter
);

Rules

Hva er en rule?

En rule er koblet til kolonner eller brukerdefinerte datatyper i en database.
Rules brukes for å definere hvilke data-verdier en kolonne kan ha.
Rules fungerer som CHECK constraints.

Hvordan lager jeg en rule?

CREATE RULE rule
  AS condition_expression

, der rule er navnet på rule'n du lager, og condition_expression er sjekken som skal utføres.
I condition_expression kan man skrive all kode som er lov å bruke i WHERE i en SELECT.

Hvordan kobler jeg rule'n min til en tabell eller datatype?

sp_bindrule [ @rulename = ] 'rule' , 
    [ @objname = ] 'object_name' 
    [ , [ @futureonly = ] 'futureonly_flag' ]

,der rule er navnet på rule'n du skal bruke, object_name er navnet på tabellen eller datatypen du skal koble til og futureonlly_flag er en verdi som kun brukes ved kobling til brukerdefinerte datatyper, og sier om rule'n skal gjelde for data som allerede er lagt inn eller ikke.

Hvordan kobler jeg fra rule'n min igjen?

sp_unbindrule [ @objname = ] 'object_name' 
    [ , [ @futureonly = ] 'futureonly_flag' ]

Hvordan sletter jeg en rule?

DROP RULE rule

Eksempel

CREATE RULE ukeSjekk
  AS @ukenr BETWEEN 1 and 52

EXEC sp_bindrule 'ukeSjekk', 'middagsListe.ukenr';
Rule bound to table column.

INSERT INTO middagsListe VALUES(1, 1, 1);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 2, 9);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 3, 2);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 4, 11);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 5, 5);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 6, 13);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(1, 7, 7);
(1 row(s) affected)

INSERT INTO middagsListe VALUES(90, 1, 10);
Server: Msg 513, Level 16, State 1, Line 1
A column insert or update conflicts with a rule imposed by a previous CREATE RULE statement. 
The statement was terminated. The conflict occurred in database 'elinkaan', table 'middagsListe', column 'ukenr'.
The statement has been terminated.

SELECT * from middagsliste;
ukenr    ukedag              rett             
-------- ------------------- ------------------- 
1        1                   1
1        2                   9
1        3                   2
1        4                   11
1        5                   5
1        6                   13
1        7                   7

(7 row(s) affected)

EXEC sp_unbindrule 'middagsListe.ukenr';
(1 row(s) affected)

Rule unbound from table column.

DROP RULE ukeSjekk;

Funksjoner

Hva er en funksjon?

En funksjon er en programsnutt bestående av et sett sql og t-sql setninger, som brukes for å behandle og evt. modifisere data i en eller flere tabeller.
En funksjon har en eller flere input-parametre, men alltid bare ett output-parameter(en dataverdi, en tabell eller en tabell med gitte kolonner).
En funksjon må startes manuelt av en bruker.

Hvilke typer funksjoner finnes?

Scalar functions: Returnerer en enkelt verdi, spesifisert med type

Hvordan lager jeg en scalar function?

CREATE FUNCTION funksjonsnavn (inputparametre)
RETURNS datatype
AS
BEGIN
  kode
  RETURN data
END

Inline table-valued functions: Returnerer en tabell

Hvordan lager jeg en inline table-valued function?

CREATE FUNCTION funksjonsnavn (inputparametre)
RETURNS Table
AS
RETURN (SELECT statement)

Multistatement table-valued functions: Returnerer en tabell med gitte kolonner
Hvordan lager jeg en multistatement inline table-valued function?
```
CREATE FUNCTION funksjonsnavn (inputparametre)
RETURNS @Tabellnavn TABLE (kolonner)
AS
BEGIN
  kode
  RETURN
END
      
```

Vi skal bare jobbe med de to første typene funksjoner

Hvordan kjører jeg en funksjon?

SELECT brukernavn.funksjonsnavn()
            eller
SELECT * from brukernavn.funksjonsnavn(variable)

Hvordan sletter jeg en funksjon?

DROP FUNCTION brukernavn.funksjonsnavn

Eksempel 1

INSERT INTO middagsListe VALUES(2, 1, 8);
INSERT INTO middagsListe VALUES(2, 2, 2);
INSERT INTO middagsListe VALUES(2, 3, 3);
INSERT INTO middagsListe VALUES(2, 4, 6);
INSERT INTO middagsListe VALUES(2, 5, 12);
INSERT INTO middagsListe VALUES(2, 6, 9);
INSERT INTO middagsListe VALUES(2, 7, 10);

INSERT INTO middagsListe VALUES(3, 1, 11);
INSERT INTO middagsListe VALUES(3, 2, 7);
INSERT INTO middagsListe VALUES(3, 3, 8);
INSERT INTO middagsListe VALUES(3, 4, 14);
INSERT INTO middagsListe VALUES(3, 5, 12);
INSERT INTO middagsListe VALUES(3, 6, 6);
INSERT INTO middagsListe VALUES(3, 7, 1);

INSERT INTO middagsListe VALUES(4, 1, 16);
INSERT INTO middagsListe VALUES(4, 2, 15);
INSERT INTO middagsListe VALUES(4, 3, 10);
INSERT INTO middagsListe VALUES(4, 4, 6);
INSERT INTO middagsListe VALUES(4, 5, 12);
INSERT INTO middagsListe VALUES(4, 6, 18);
INSERT INTO middagsListe VALUES(4, 7, 19);

CREATE FUNCTION antCounter(@rett varchar(25))
RETURNS int
AS
BEGIN
  DECLARE @teller int

  SELECT @teller=COUNT(mr.rettid)
  FROM middagsListe ml, matRetter mr
  WHERE mr.navn = @rett
  AND ml.rett = mr.rettid
  GROUP BY(mr.rettid)

  RETURN @teller;
END

SELECT elinkaan.antCounter('pizza') AS 'Antall ganger pizza til middag';
Antall ganger pizza til middag
------------------------------
2

(1 row(s) affected)


DROP FUNCTION elinkaan.antCounter;

Eksempel 2

CREATE FUNCTION finnMestPopRetter()
RETURNS Table
AS
RETURN (
  SELECT mr.navn
  FROM middagsListe ml, matRetter mr
  WHERE ml.rett = mr.rettid
  GROUP BY(mr.navn)
  HAVING COUNT(mr.navn) >= ALL(
   SELECT COUNT(rett)
   FROM middagsListe
   GROUP BY(rett)
   )
)

SELECT navn AS 'Mest populær(e) rett(er)' FROM elinkaan.finnMestPopRetter();
Mest populær(e) rett(er)
-----------------------
reker
taco

(1 row(s) affected)

DROP FUNCTION elinkaan.finnMestPopRetter;

Eksempel 3

CREATE FUNCTION genererLesbarListe (@ukenr int)
RETURNS Table
AS
RETURN(
  SELECT ml.ukenr, ud.dagnavn, mr.navn AS rett
  FROM middagsListe ml, ukeDager ud, matRetter mr
  WHERE ml.ukenr = @ukenr
  AND ml.rett = mr.rettid
  AND ml.ukedag = ud.dagnr
)

SELECT * FROM elinkaan.genererLesbarListe(1);
ukenr	dagnavn	rett
-----	-------	-------------
1	mandag	kjøttkaker
1	tirsdag	pastasalat
1	onsdag	pizza
1	torsdag	fylt torsk
1	fredag	lasagne
1	lørdag	fylt paprika
1	søndag	oksestek

(7 row(s) affected)

DROP FUNCTION elinkaan.genererLesbarListe;

Triggere

Hva er en trigger?

En trigger er en programsnutt bestående av sql og t-sql kode som starter/blir avfyrt automatisk dersom en bestemt tabell blir modifisert. En trigger kan bli avfyrt som resultat av INSERT, UPDATE eller DELETE.
En trigger kan ikke startes manuelt av en bruker.

Hvilke typer triggere finnes det?

Det finnes 2 typer triggere:

Instead of trigger: Forhindrer at data blir lagt inn i en tabell

Hvordan lager jeg en instead of trigger?

CREATE TRIGGER triggernavn ON tabell
INSTEAD OF Insert, Update | Insert | Update | Delete
AS kode
GO

After trigger: Gjør noe med data som blir lagt inn, eller lar evt. vær å legge inn data ved å kjøre en rollback ved behov
Hvordan lager jeg en after trigger?
```
CREATE TRIGGER triggernavn ON tabell
AFTER  Insert, Update | Insert | Update | Delete
AS kode
GO
        
```

Hvordan sletter jeg en trigger?

DROP TRIGGER triggernavn

Eksempel 1

CREATE TRIGGER hindreNyeUkedager ON ukeDager
INSTEAD OF Insert
AS
  PRINT 'Vet du ikke at det ikke er mer enn 7 dager i uka?'
GO

INSERT INTO ukeDager VALUES(8, 'farsdag');
Vet du ikke at det ikke er mer enn 7 dager i uka?

(1 row(s) affected)

DROP TRIGGER hindreNyeUkedager;

Eksempel 2

CREATE TABLE matlisteLogg(
  ukenr int,
  dagnr int,
  gmlrett varchar(25),
  nyrett varchar(25),
  log_naar datetime,
  log_hvem sysname, 
  PRIMARY KEY (ukenr, dagnr, log_naar)
);

CREATE TRIGGER matLogg ON middagsListe
AFTER Insert, Update
AS
  DECLARE @nyuke int,
          @nydag int,
          @nyrett int,
          @gmlrett int

  SELECT @nyuke = ukenr, @nydag = ukedag, @nyrett = rett
  FROM inserted;

  IF(@nyuke IS NULL)
  BEGIN
    SELECT @nyuke = ukenr, @nydag = ukedag, @gmlrett = rett
    FROM deleted;
  END
  ELSE
  BEGIN
    SELECT @gmlrett = rett
    FROM deleted;
  END

  INSERT INTO matlisteLogg VALUES (@nyuke, 
                                   @nydag,
                                   @gmlrett, @nyrett,
                                   CURRENT_TIMESTAMP,
                                   CURRENT_USER)

GO

INSERT INTO middagsListe VALUES(5, 1, 18);
(1 row(s) affected)

UPDATE middagsListe SET rett = 19
WHERE ukenr = 5
AND ukedag = 1;
(1 row(s) affected)

SELECT * FROM middagsListe;
ukenr       ukedag      rett        
----------- ----------- ----------- 
1           1           1
1           2           9
1           3           2
1           4           11
1           5           5
1           6           13
1           7           7
2           1           8
2           2           2
2           3           3
2           4           6
2           5           12
2           6           9
2           7           10
3           1           11
3           2           7
3           3           8
3           4           14
3           5           12
3           6           6
3           7           1
4           1           16
4           2           15
4           3           10
4           4           6
4           5           12
4           6           18
4           7           19
5           1           19

(29 row(s) affected)

SELECT * FROM matlisteLogg;
ukenr       dagnr       gmlrett              nyrett               log_naar                                        log_hvem
----------- ----------- -------------------- -------------------- ----------------------------------------------- ----------------------
5           1           NULL                 18                   2004-02-05 15:09:34.000                         elinkaan
5           1           18                   19                   2004-02-05 15:15:52.170                         elinkaan

(2 row(s) affected)

DROP TRIGGER matLogg;

Eksempel 3

CREATE TRIGGER sjekkUkenavnOgNr ON ukeDager
AFTER Insert, Update
AS
  DECLARE @dagnavn varchar(7),
           @dagnr int,
           @feil int

  SELECT @feil = -1

  SELECT @dagnr = dagnr, @dagnavn = dagnavn
  FROM inserted

  IF(@dagnr IS NULL)
  BEGIN
    SELECT @dagnr = dagnr, @dagnavn = dagnavn
    FROM deleted;
  END

   IF @dagnr <= 0 OR @dagnr > 7
   BEGIN
      PRINT ('Feil: dagnr må være mellom 1 og 7')
      SELECT @feil = 0
   END

   IF LOWER(@dagnavn) NOT IN ('mandag', 'tirsdag', 'onsdag', 'torsdag', 'fredag', 'lørdag', 'søndag')
   BEGIN
      PRINT ('Feil: dagnavn må være mandag, tirsdag, onsdag, torsdag, fredag, lørdag eller søndag')
      SELECT @feil = 0
   END

   IF @feil = 0
   BEGIN
      ROLLBACK TRANSACTION
   END
GO

INSERT INTO ukeDager VALUES(8, 'farsdag');
Feil: dagnr må være mellom 1 og 7
Feil: dagnavn må være mandag, tirsdag, onsdag, torsdag, fredag, lørdag eller søndag

UPDATE ukedager SET dagnavn = 'Mornda' WHERE dagnr = 1;
Feil: dagnavn må være mandag, tirsdag, onsdag, torsdag, fredag, lørdag eller søndag

SELECT * FROM ukeDager
dagnr       dagnavn 
----------- ------- 
1           mandag
2           tirsdag
3           onsdag
4           torsdag
5           fredag
6           lørdag
7           søndag

(7 row(s) affected)

DROP TRIGGER sjekkUkenavnOgNr;

Fil med tabeller og data

Elin K. Ajer Andreassen, 2004

Rules, funksjoner og triggere

Eksempel-tabeller

matRetter

ukeDager

middagsListe

Rules

Hva er en rule?

Hvordan lager jeg en rule?

Hvordan kobler jeg rule'n min til en tabell eller datatype?

Hvordan kobler jeg fra rule'n min igjen?

Hvordan sletter jeg en rule?

Eksempel

Funksjoner

Hva er en funksjon?

Hvilke typer funksjoner finnes?

Hvordan lager jeg en scalar function?

Hvordan lager jeg en inline table-valued function?

Hvordan lager jeg en multistatement inline table-valued function?

Hvordan kjører jeg en funksjon?

Hvordan sletter jeg en funksjon?

Eksempel 1

Eksempel 2

Eksempel 3

Triggere

Hva er en trigger?

Hvilke typer triggere finnes det?

Hvordan lager jeg en instead of trigger?

Hvordan lager jeg en after trigger?

Hvordan sletter jeg en trigger?

Eksempel 1

Eksempel 2

Eksempel 3