Basi di dati
(modulo 2)
Prof. Giovanni Giuffrida
Stanza: 362 tel: 095 738 3051
e-mail: [email protected]
Basi di dati II
1
Programma

Progettazione di basi di dati
 Il
modello entity-relationship (ER)
 Progettazione


Logica, fisica e concettuale
Oggetti SQL
 Vincoli,
Viste, Procedure
 Trigger
 Esempi
su DB commerciali: Oracle e DB2
 Esercitazioni
Basi di dati II
2
Programma, cont.


Normalizzazione di schemi relazionali
Aspetti sistemistici dei DBMS




Cataloghi, schemi
Transazioni
Piani di esecuzione
SQL Avanzato





Sequenze
Viste materializzate
Query multidimensionali (OLAP)
Query ricorsive
Esercitazioni
Basi di dati II
3
Programma, cont.

Linguaggi procedurali e interfacce
 PHP,

JDBC2, QBE
Amministrazione di basi di dati
 Controllo
accessi
 Monitoring
 Tuning
Basi di dati II
4
Vincoli d’integrita’

Riguardano i valori ammissibili degli attributi di una tupla



Vincoli Intrarelazionali: nell’ambito della stessa relazione
Vincoli Referenziali (o Interrelazionali): tra diverse relazioni
Vengono controllati durante le tre possibili operazioni di
modifica SQL

INSERT,DELETE e UPDATE

Devono essere sempre soddisfatti altrimenti la transazione
fallisce
Oppure, l’utente puo’ opzionalmente definire della azioni
(correttive) da intraprendere per ripristinare l’integrita’

Basi di dati II
5
A cosa servono i vincoli d’integrita’
Migliorare la qualita’ dei dati
 Arricchire semanticamente la base di dati
 La loro definizione e’ parte del processo di
progettazione del data base
 Usati internamente dal sistema per
ottimizzare l’esecuzione

Basi di dati II
6
Esempio di DB semanticamente errato
Esami Studente Voto Lode Corso
276545
32
01
276545
30 e lode 02
787643
27 e lode 03
739430
24
04
Studenti Matricola Cognome Nome
276545
Rossi
Mario
Neri
Piero
787643
787643
Bianchi
Luca
Basi di dati II
7
Vincoli sui valori della tupla

NOT NULL



DEFAULT (Costante|NULL)



implicito se l’attributo fa parte di una chiave primaria
Esempio: campo matricola nella tabella Studente
assegna un valore di default per ogni inserimento se non specificato
Esempio: DEFAULT CURRENT DATE
CHECK Condizione
Dove “Condizione” e’ un’espressione booleana per il controllo di attributi, costanti
ed espressioni
 Dev’essere valutata True per la corretta esecuzione della transazione
 Vincoli sul dominio:


Esempio: specifica i valori ammissibili nell’attributo Voto della tabella Esami:



Voto NOT NULL
(18  Voto AND Voto  30)
Vincoli basati su piu’ attributi

(Lode <> ‘Si’) OR (Voto = 30)
Basi di dati II
8
Definizione vincoli intrarelazionali

PRIMARY KEY [Nome Chiave] “(”Attributo{,Attributo} “)”

gli attributi devono essere dichiarati tutti NOT NULL
 Esempio: Attributo Matricola nella relazione Studenti

UNIQUE “(”Attributo{,Attributo} “)”
definisce una chiave con uno o piu’ attributi
 Esempio: (Nome,Cognome,DataDiNascita)
 Nota:



Nome not null unique,
Cognome not null unique
E’ diverso da:

Nome not null,
Cognome not null,
UNIQUE (Nome, Cognome)
Basi di dati II
9
Vincoli d’integrita’ referenziali (o interrelazionali)


Tuple di relazioni diverse sono correlati per
mezzo del valore di chiavi (primarie)
Servono a garantire che i valori in una certa
tabella facciano riferimento a valori reali di
un’altra tabella
 Esempio:

Esami(...,Matricola), Studenti( Matricola,...)
Basi di dati II
10
Infrazioni
Codice
Data
Vigile
34321
1/2/95
3987
MI
39548K
53524
4/3/95
3295
TO
E39548
64521
5/4/96
3295
PR
839548
73321
5/2/98
9345
PR
839548
Vigili
Prov Numero
Matricola Cognome
Nome
3987
Rossi
Luca
3295
Neri
Piero
9345
Neri
Mario
7543
Mori
Gino
Basi di dati II
11
Infrazioni
Codice
Data
Vigile
Prov Numero
34321
1/2/95
3987
MI
39548K
53524
4/3/95
3295
TO
E39548
64521
5/4/96
3295
PR
839548
73321
5/2/98
9345
PR
839548
Auto Prov Numero Cognome
Nome
MI
39548K
Rossi
Mario
TO
E39548
Rossi
Mario
PR
839548
Neri
Luca
Basi di dati II
12
Vincolo di integrità referenziale
Un vincolo di integrità referenziale
(“foreign key”) fra gli attributi X di una relazione
R1 e un’altra relazione R2 impone ai valori su X
in R1 di comparire come valori della chiave
primaria di R2
 Nell‘esempio precedente:

 vincoli di integrità referenziale fra:
 l’attributo Vigile della relazione INFRAZIONI e la relazione
VIGILI
 gli attributi Prov e Numero di INFRAZIONI e la relazione AUTO

NULL per evitare il controllo del vincolo
Basi di dati II
13
Violazione di vincolo di integrità
referenziale
Infrazioni
Codice
34321
53524
64521
73321
Data
1/2/95
4/3/95
5/4/96
5/2/98
Vigile
3987
3295
3295
9345
Prov
MI
TO
PR
PR
Auto Prov Numero Cognome
MI
TO
PR
E39548
F34268
839548
Basi di dati II
Rossi
Rossi
Neri
Numero
39548K
E39548
839548
839548
Nome
Mario
Mario
Luca
14
Integrità referenziale e valori nulli
Impiegati
Progetti
Matricola Cognome Progetto
34321
Rossi
IDEA
53524
Neri
XYZ
NULL
64521
Verdi
73032
Bianchi
IDEA
Codice
IDEA
XYZ
BOH
Inizio
01/2000
07/2001
09/2001
Basi di dati II
Durata
36
24
24
Costo
200
120
150
15
Azioni compensative


Esempio: Viene eliminata una tupla causando cosi' una
violazione
Tre possibili azioni

Rifiuto dell'operazione


Eliminazione in cascata


ON DELETE NO ACTION (la piu’ diffusa nei DBMS)
ON DELETE CASCADE: cancella tutte le tuple con valori della
chiave esterna corrispondenti alla chiave primaria delle tuple
cancellate
Introduzione di valori nulli

ON DELETE SET NULL assegna il valore NULL agli attributi della
chiave esterna
Basi di dati II
16
Rifiuto della cancellazione
Impiegati
Progetti

Matricola Cognome Progetto
34321
Rossi
IDEA
53524
Neri
XYZ
NULL
64521
Verdi
73032
Bianchi
IDEA
Codice
IDEA
XYZ
BOH
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
La transazione fallisce e XYZ non puo’ essere cancellato
dalla relazione Progetti Basi di dati II
17
Eliminazione in cascata
Impiegati
Progetti

Matricola Cognome Progetto
34321
Rossi
IDEA
53524
Neri
XYZ
NULL
64521
Verdi
73032
Bianchi
IDEA
Codice
IDEA
XYZ
BOH
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
La transazione termina e XYZ viene cancellato anche
dalla relazione ImpiegatiBasi di dati II
18
Introduzione di valori nulli
Impiegati
Progetti

Matricola Cognome Progetto
34321
Rossi
IDEA
NULL
53524
Neri
XYZ
NULL
64521
Verdi
73032
Bianchi
IDEA
Codice
IDEA
XYZ
BOH
Inizio
01/2000
07/2001
09/2001
Durata
36
24
24
Costo
200
120
150
La transazione termina e all’attributo Impiegati.Progetto
viene assegnato NULL Basi di dati II
19
Vincoli multipli su più attributi
Incidenti
Codice Data ProvA NumeroA ProvB NumeroB
34321 1/2/95
TO
E39548
MI
39548K
64521 5/4/96
PR
839548
TO
E39548
Auto Prov Numero Cognome
Nome
MI
39548K
Rossi
Mario
TO
E39548
Rossi
Mario
PR
839548
Neri
Luca
Basi di dati II
20
Vincoli Interrelazionali, Sintassi

FOREIGN KEY [NomeChiaveEsterna]
“(”Attributo{,Attributo} “)”
REFERENCES TabellaRef
ON DELETE {NO ACTION,CASCADE,SET NULL}


dove per la TabellaRef e’ stata definita una chiave primaria
Quindi: impedisce l’inserimento di tuple con il valore
della chiave esterna che non corrisponde ad un valore
della chiave primaria della TabellaRef
Basi di dati II
21
Esempio Riassuntivo

CREATE TABLE Clienti (
CodiceCliente CHAR(3) UNIQUE NOT NULL,
Nome CHAR(30)
NOT NULL,
Citta’ CHAR(30) NOT NULL,
Sconto INTEGER
NOT NULL
CHECK(Sconto>0 AND Sconto<100),
PRIMARY KEY pk_Clienti(CodiceCliente))

CREATE TABLE Agenti (
CodiceAgente CHAR(3) UNIQUE NOT NULL,
Nome CHAR(30)
NOT NULL,
Zona CHAR(8) NOT NULL,
Supervisore CHAR(3),
Commissione INTEGER)
PRIMARY KEY pk_Agenti(CodiceAgente),
CHECK (Supervisore  CodiceAgente OR Supervisore IS NULL)
Basi di dati II
22
Esempio Riassuntivo

CREATE TABLE Ordini(
NumOrdine CHAR(3) NOT NULL,
CodiceCliente CHAR(3) NOT NULL,
CodiceAgente CHAR(3) NOT NULL,
Data CHAR(8) NOT NULL,
Prodotto CHAR(3) NOT NULL,
Ammontare INTEGER NOT NULL CHECK (Ammontare > 100)
PRIMARY KEY pk-Ordini (NumOrdine)
FOREIGN KEY fk_ClienteOrdine (CodiceCliente)
REFERENCES Clienti
ON DELETE NO ACTION
FOREIGN KEY fk_AgenteOrdine (CodiceAgente)
REFERENCES Agenti
ON DELETE NO ACTION
Basi di dati II
23
Viste (View)



Oltre alle tabelle di base che fanno parte dello
schema si possono creare delle tabelle
ausiliarie virtuali
Sono “virtuali” in quanto sembrano tabelle a tutti
gli effetti ma sono delle relazioni “create al volo”
Utilizzate per vari scopi:
 Semplificazione
 Protezione dati
 Scomposizione query
complesse
 Riorganizzazione dati secondo nuovi schemi
 Etc.
Basi di dati II
24
Definizione VIEW

Sintassi creazione VIEW:
CREATE VIEW NomeVista
[“(” Attributo {,Attributo} “)”]
AS Query-Select
Basi di dati II
25
Esempio definizione VIEW


CREATE VIEW
MediaVoti (Matricola,Media)
AS
SELECT Matricola, AVG(Voto)
FROM Esami
GROUP BY Matricola
Esecuzione:
SELECT *
FROM MediaVoti
Basi di dati II
26
Le VIEW possono essere usate
come tabelle


SELECT Nome, Media
FROM Studenti, MediaVoti
WHERE
Studenti.Matricola = MediaVoti.Matricola
Le VIEW possono essere distrutte alla pari di tabelle





DROP (TABLE | VIEW) Nome [RESTRICT|CASCADE]
Con RESTRICT non viene cancellata se e’ utilizzata in altre viste
Con CASCADE verranno rimosse tutte le viste che usano la
View o la Tabella rimossa
Non tutti i sistemi permettono l’uso di RESTRICT e CASCADE
La distruzione di una VIEW non altera le tabelle su cui la VIEW
si basa
Basi di dati II
27
Le VIEW possono essere usate
come tabelle


Una VIEW puo’ essere definita sulla base di un’altra
VIEW
Nelle prime versioni di SQL non era possibile modificare
una VIEW tramite Insert, Delete, Update


Non piu’ vero nei nuovi DBMS (Vedremo dopo)
Che succede se una tabella usata in una VIEW viene
alterata o cancellata (senza specificare RESTRICT o
CASCADE)?

Dipende dal DBMS:



la VIEW viene marcata ‘inoperative’, oppure
La modifica/cancellazione viene negata
Etc.
Basi di dati II
28
Uso delle VIEW per query
complesse
Semplificare query complesse
 Esempio: non possiamo scrivere
SELECT AVG(COUNT(*))
FROM AGENTI
GROUP BY ZONE

 AVG
deve agire sui valori di un attributo.
Basi di dati II
29
Uso delle VIEW per query
complesse

CREATE VIEW AgPerZona (Zona,NumAg)
AS
SELECT Zona,COUNT(*)
FROM AGENTI
GROUP BY Zona

SELECT AVG(NumAg)
FROM AgPerZona

DROP AgPerZona
Basi di dati II
30
Uso delle VIEW per Sicurezza



CREATE VIEW EsamiPublici
AS SELECT Corso,Voto
FROM Esami
Data la tabella
ClientiBanca(Nome,Indirizzo,Saldo)
CREATE VIEW ClientiInd
AS SELECT Nome,Indirizzo
FROM ClientiBanca
Basi di dati II
31
Mascherare l’organizzazione
logica dei dati tramite VIEW

Immaginiamo la seguente tabella:


Agenti( CodiceAgente, Nome, Zona, Commissione, Supervisore)
Per riorganizzazione aziendale si decide di assegnare un Supervisore ad
una zona intera invece del singolo agente
1) CREATE TABLE Zone (Zona CHAR(8), Supervisore CHAR(3))
AS SELECT DISTINCT Zona,Supervisore
FROM Agenti
2) CREATE TABLE NuoviAgenti
AS SELECT CodiceAgente,Nome,Zona,Commissione
FROM Agenti
3) DROP Agenti
4) CREATE VIEW Agenti
AS SELECT *
FROM NuoviAgenti NATURAL JOIN Zone
Basi di dati II
32
Aggiornamento delle VIEW



Le operazioni INSERT/UPDATE/DELETE sulle
VIEW non erano permesse nelle prime edizioni
di SQL
I nuovi DBMS permettono di farlo con certe
limitazioni dovute alla definizione della VIEW
stessa
Che senso ha aggiornare una VIEW? Dopotutto
si potrebbe aggiornare la tabella di base
direttamente…
Basi di dati II
33
Aggiornamento delle VIEW, cont.


… utile nel caso di accesso dati controllato
Esempio:



Il personale della segreteria non puo’ accedere ai dati sullo stipendio
ma puo’ modificare gli altri campi della tabella, aggiungere e/o
cancellare tuple
Si puo’ controllare l’accesso tramite la definizione della VIEW:


Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio)
CREATE VIEW Impiegato2 AS
SELECT Nome, Cognome, Dipart, Ufficio
FROM Impiegato
INSERT INTO Impiegato2 VALUES (…)


Stipendio verra’ inizializzato a Null
Se Null non e’ permesso per Stipendio l’operazione fallisce
Basi di dati II
34
Aggiornamento VIEW 2
Immaginiamo la seguente VIEW:
CREATE VIEW ImpiegatoRossi
AS
SELECT *
FROM Impiegato
WHERE Cognome=‘Rossi’
 La seguente operazione ha senso:

 INSERT
INTO ImpiegatoRossi (…’Rossi’,…)
Basi di dati II
35
Aggiornamento VIEW 2, cont.

Ma che succede nel caso di:




INSERT INTO ImpiegatoRossi (…’Bianchi’,…)
In genere e’ permesso, finisce nella tabella base ma non e’
visibile dalla VIEW
Si puo’ controllare tramite l’opzione “WITH CHECK OPTION”:
CREATE VIEW ImpiegatoRossi
AS
SELECT *
FROM Impiegato
WHERE Cognome=‘Rossi’
WITH CHECK OPTION
Adesso l’insert con ‘Bianchi’ fallisce, quella con ‘Rossi’
viene invece eseguita.
Basi di dati II
36
Aggiornamento VIEW 3





Consideriamo il seguente caso:

Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio)


Dipartimenti( Dipart, Indirizzo)
CREATE VIEW IMP_IND AS
SELECT Nome, Cognome, d.dipart, indirizzo
FROM Impiegato i join Dipartimenti d ON i.Dipart=d.Dipart
Un INSERT sulla VIEW IMP_IND dovrebbe inserire su entrambe le
tabelle base
In alcuni casi potrebbe inserire in una ma non nell’altra
In genere quest’operazione non e’ consentita
Alcuni DBMS consentirebbero l’INSERT se “Impiegati.Dipart” fosse
una foreign key su “Dipartimenti.Dipart” e quest’ultima fosse chiave
primaria
Basi di dati II
37
Aggiornamento VIEW, riepilogo


In genere una VIEW definita su una singola tabella e’
modificabile se gli attributi della VIEW contengono la
chiave primaria (e altre chiavi)
In genere VIEW definite su piu’ tabelle non sono
aggiornabili



Alcuni DBMS, come discusso prima, lo permettono nel caso
certe condizioni, molto restrittive, siano rispettate
VIEW che usano funzioni di aggregazione non sono
aggiornabili
PRINCIPIO di base per l’aggiornamento delle VIEW:

Ogni riga ed ogni colonna della VIEW deve corrispondere ad
una ed una sola riga ed una ed una sola colonna della tabella
base
Basi di dati II
38
Aspetti procedurali dei DBMS
Procedure: Programmi memorizzati nel
DBMS che vengono eseguiti su esplicita
richiesta degli utenti.
 Trigger: Programmi memorizzati nel
DBMS che vengono attivati
automaticamente dopo le operazioni di
modifica sulle tabelle

Basi di dati II
39
Procedure


Possono essere costituite da un unico comando SQL
parametrizzato
I moderni DBMS offrono un linguaggio procedurale piu’ ricco



Oracle: PL/SQL
Sybase: Transact/SQL
IBM-DB2
 MySQL (Ver.5)??

Possono anche essere scritte in linguaggi standard:



C/C++, Java
Compilate come oggetti esterni integrati dal DBMS
In alcuni casi possono eseguire azioni esterne:


Cancellare un file
Spedire un’email
Basi di dati II
40
Linguaggio procedurale
Complementano la natura dichiarativa di
SQL
 Costrutti tipo: FOR, WHILE, LOOP, IF, etc.
 Scansione iterativa di tabelle

Basi di dati II
41
Esempio linguaggio procedurale DB2

Comando IF/THEN
BEGIN ATOMIC
DECLARE cur INT;
SET cur = MICROSECOND(CURRENT TIMESTAMP);
IF cur > 600000 THEN
UPDATE staff
SET name = CHAR(cur)
WHERE id = 10;
ELSEIF cur > 300000 THEN
UPDATE staff
SET name = CHAR(cur)
WHERE id = 20;
ELSE
UPDATE staff
SET name = CHAR(cur)
WHERE id = 30;
END IF;
END
Basi di dati II
42
Esempio linguaggio procedurale DB2

Comando FOR per scansione tabella
BEGIN ATOMIC
FOR V1 AS
SELECT dept AS dname, max(id) AS max_id
FROM staff
GROUP BY dept
HAVING COUNT(*) > 1
ORDER BY dept
DO
UPDATE staff
SET id = id * -1
WHERE id = v1.max_id;
UPDATE staff SET dept = dept / 10
WHERE dept = v1.dname AND dept < 30;
END FOR;
END
Basi di dati II
43
Esempio linguaggio procedurale DB2

Comando WHILE per scansione tabella
BEGIN ATOMIC
DECLARE c1, C2 INT DEFAULT 1;
WHILE c1 < 10 DO
WHILE c2 < 20 DO
SET c2 = c2 + 1;
END WHILE;
SET c1 = c1 + 1;
END WHILE;
UPDATE staff
SET salary = c1 ,comm = c2
WHERE id = 10;
END
Basi di dati II
44
Vantaggi delle procedure
Consentono di condividere fra gli utenti
delle attivita’ comuni, in modo da
centralizzare la manutenzione, la modifica
etc..
 Unificano la semantica di certe operazioni
sul DB per ogni applicazione
 Possono controllare in modo centralizzato
certi vincoli d’integrita’ non esprimibili nelle
tabelle.

Basi di dati II
45
Vantaggi delle procedure
Riducono il traffico sulla rete dovuto ad
applicazioni remote, infatti invece di agire
interattivamente con il DBMS l’utente
spedisce una volta per tutte una chiamata
alla procedura ricevendone la risposta.
 Garantiscono la sicurezza dei dati
consentendo a certi utenti di accedere ai
dati attraverso certe procedure e non
direttamente

Basi di dati II
46
Basi di dati “Attive”

Trigger: Regole basate sul paradigma Event-Condition-Action
(ECA) incorporate nella base di dati


I DBMS attivi hanno un comportamento “reattivo” in contrasto col
passivo della basi di dati tradizionali




Struttura tipica di una regola/trigger:
when Event
if Condition
then Action
Eseguono sia transazioni utente che trigger
I trigger sono simili alle procedure ma vengono invocati
automaticamente in seguito alle operazioni di modifica della base di
dati (INSERT/DELETE/UPDATE)
Fanno parte della definizione della base di dati
Arricchiscono semanticamente lo schema relazionale
Basi di dati II
47
Basi di dati “Attive”, cont.



La loro sintassi e’ stata standardizzata in SQL-1999
Sistemi relazionali commerciali (e non) includono i
trigger fin dagli anni 80
Cio’ ha causato difformita’ di sintassi difficilmente
riconciliabile



non esiste ancora un’implementazione “Standard” da un punto
di vista sintattico
Estensione del “CHECK” in quanto permettono di
operare (modificare) su altre tabelle
Possono anche scatenare azioni esterne al DB

Spedire email, cancellare file, etc.
Basi di dati II
48
Utilizzo trigger

Business rules, parte della procedura “di business” applicativa
(normalmente eseguite in modo asincrono dall’applicazione)





Automazione magazzino con riordino automatico
Spedizione solleciti
Controllo attivita’ conti bancari/carte di credito
Acquisto/Vendita automatica strumenti finanziari
Auditing e Logging, memorizzazione eventi per controlli

Esempi:





Storico delle modifiche effettuate su una tabella per recupero dati
Elenco dei login effettuati (da chi e quando)
Controllo delle attivita’ (chi ha fatto cosa e quando)
Etc.
Version Management

Conservare varie versioni dello stato della base di dati nel tempo
Basi di dati II
49
Utilizzo Trigger

Duplicazione database
 trasparente
tramite l’uso dei trigger
 Implementazione database distribuiti


Vincoli d’integrita’ complessi non esprimibili
con il comando “CHECK”
Workflow management
 Esempio: Assegnare
sostituto per chiamate d’urgenza
Basi di dati II
50
Paradigma Event-Condition-Action

Semantica operativa:
Quando succede l’evento…
 … se la condizione e’ soddisfatta…
 … esegui l’azione specificata



Event: Aggiornamento dati tramite INSERT, UPDATE o DELETE
Condition: Predicato SQL



Opzionale
Action: Sequenza di comandi SQL o SQL procedurale, ROLLBACK,
etc.
Ogni trigger e’ associato ad una sola tabella e viene attivato dalle
operazioni dirette a quella tabella
Basi di dati II
51
Esempi trigger

Espressi in sintassi non-standard:
CREATE RULE ControlloStipendio ON Impiegati
WHEN Inserted, Deleteted, Updated(Stipendio)
IF (select avg(stipendio) from impiegati) > 100
THEN update impiegati set stipendio=stipendio * 0.9
CREATE RULE ControlloRicchi ON Impiegati
WHEN Inserted
IF EXISTS ( select * from INSERTED
where stipendio>100)
THEN Insert into ImpiegatiRicchi ( select * from INSERTED
where stipendio>100)
Basi di dati II
52
Esempio esecuzione trigger
La
tabella impiegati inizialmente:
Inseriamo:
Aldo
90
Maria
90
Luca
110
(Piero, 150) e (Mario, 120)
Aldo
90
Aldo
Maria
90
Luca
110
Luca
99
Piero
150
Piero
135
Mario
120
Mario
108
Il
trigger
ControlloStipendio parte: Maria
81
81
…
Basi di dati II
53
Esempio esecuzione trigger,cont
Il
trigger ControlloStipendio parte di nuovo: Ricorsione
Aldo
73
Maria
73
Luca
89
Piero
121
Mario
97
Inoltre,
alla fine la tabella ImpiegatiRicchi conterra’:
Piero
121
Basi di dati II
54
Granularita’

Due tipi:



Row-level


Il trigger viene eseguito una volta per ogni tupla coinvolta
nell’operazione di modifica
Statement-level


Row-level
Statement-level
Il trigger viene eseguito una sola volta per tutte le tuple coinvolte
Richiedono una sintassi diversa


Row-level: Riferimento ad una riga sola
Statement-level: Riferimento ad una tabella
Basi di dati II
55
Tuple e Tabelle di transizione

Tuple e tabelle temporanee visibili all’interno del trigger eseguito che
contengono la porzione di dati manipolati dal comando SQL che ha attivato
il trigger




Possono essere usate sia nella condizione che nell’azione
Nella granularita’ statement-level il trigger usa delle tabelle di transizione:




Servono per accedere ai dati manipolati dal comando che ha scatenato il trigger
Vengono usate in modo standard dall’SQL del trigger
DELETE: Una tabella di transizione con tutte le tuple cancellate
INSERT: Una tabella di transizione con tutte le tuple inserite
UPDATE: Una tabella di transizione con tutte le tuple con i vecchi valori ed una
con tutte le tuple con i nuovi valori
Nella granularita’ row-level il trigger usa delle tuple di transizione:



DELETE: Una per la tupla cancellata
INSERT: Una per la tupla inserita
UPDATE: Una per la tupla con i vecchi valori ed una con i nuovi valori
Basi di dati II
56
Modo di esecuzione del trigger


Indica se far partire il trigger prima (BEFORE) o dopo
(AFTER) l’esecuzione fisica del comando impartito
BEFORE



viene normalmente usato per modificare i dati del comando
impartito prima dell’esecuzione (fisica) del comando stesso…
(vedi esempio)
In genere limitato nei comandi che puo’ eseguire
AFTER


Molto piu’ comune
Pieno accesso all’SQL e SQL procedurale
Basi di dati II
57
Esempio Trigger BEFORE in DB2
CREATE TRIGGER T1
NO CASCADE BEFORE INSERT ON IMPIEGATO
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
WHEN ( N.STIPENDIO > (SELECT MAX(STIPENDIO)
FROM IMPIEGATO))
SET N.STIPENDIO = (SELECT MAX(STIPENDIO)
FROM IMPIEGATO)
Basi di dati II
58
Esempio granularita’ per AFTER in
DB2
create table progetti(
codice char(10) not null primary key,
Inizio date,
Durata int,
Costo int
)
create table progetti_cancellati(
codice char(10) not null,
cancellato_il date with default current date
)
create trigger storico_prog_canc
after delete on progetti
referencing old as o
for each row mode db2sql
begin atomic
insert into progetti_cancellati
values( o.codice, current date);
end
create trigger storico_prog_canc2
after delete on progetti
referencing old_table as o
for each statement mode db2sql
begin atomic
insert into progetti_cancellati
select codice, current date
from o;
end
Basi di dati II
59
Esecuzione del trigger

Immediata




Differita



Non appena il comando viene eseguito
Nel mezzo della transazione
Fa parte della stessa transazione
Esecuzione rimandata alla fine della transazione
Fa parte della stessa transazione
Disaccoppiata

Esecuzione del trigger fa parte di una transazione separata, viene quindi
eseguito anche se la transazione precedente fallisce
 Non disponibile in tutti i sistemi

Nota che questi modi di esecuzione possono essere in conflitto con la
semantica del BEFORE/AFTER

Dipende dal DBMS
Basi di dati II
60
Esempio sintassi trigger Oracle

Sintassi Oracle:
CREATE TRIGGER NomeTrigger
TipoTrigger (TipoOperazione{OR TipoOperazione})
[OF Attributo] ON NomeTabella
[FOR EACH ROW]
[WHEN “(”Condizione“)”]
Procedura in PL/SQL

TipoTrigger ::= (BEFORE|AFTER)

TipoOperazione ::= (DELETE|INSERT|UPDATE)

FOR EACH ROW specifica che l’azione deve essere
ripetuta su ogni n-upla (oppure una volta per tutte)
Basi di dati II
61
Esempio Oracle PL/SQL



Supponiamo che non si accettano ordini con uno scoperto >2.500
CREATE TRIGGER ControlloFido
BEFORE INSERT ON Ordini
DECLARE DaPagare NUMBER;
BEGIN
SELECT SUM(Ammontare) INTO DaPagare
FROM Ordini
WHERE CodiceCliente = :new.CodiceCliente;
IF DaPagare > 2.500 - :new.Ammontare
THEN
RAISE_APPLICATION_ERROR (-2061, ‘fido superato’);
END IF;
END
:new valore da inserire o modificato, :old e’ il valore precedente
Basi di dati II
62
Creazione ed aggiornamento
automatico di una tabella


CREATE TABLE Totali(CodiceAgente CHAR(3), TotaleOrdini INTEGER)
CREATE TRIGGER aggiornaTotali
AFTER INSERT ON Ordini
FOR EACH ROW
DECLARE esiste NUMBER;
BEGIN
SELECT COUNT(*) INTO esiste FROM Totali
WHERE CodiceAgente = :new.CodiceAgente;
IF esiste = 0 $agente non ancora presente$
THEN
INSERT INTO Totali
VALUES (:new.CodiceAgente, :new.Ammontare);
ELSE UPDATE Totali SET TotaleOrdini = TotaleOrdini +
:new.Ammontare
WHERE CodiceAgente = :new.CodiceAgente;
END;
Basi di dati II
63
Cancellazione automatica



Per cancellare la riga dei Totali relativa ad un agente che
viene licenziato
CREATE TRIGGER cancellaAgente
AFTER DELETE ON Agenti
FOR EACH ROW
BEGIN
DELETE FROM Totali
WHERE CodiceAgente = :old.CodiceAgente;
END;
Nota che per il DELETE e’ stato usato “:old”
Basi di dati II
64
Vantaggi sull’uso dei Trigger




Knowledge Independence, si semplificano le
applicazioni che non devono fare i controlli dei trigger
Permettono di centralizzare i controlli che quindi non
possono essere evitati dagli utenti del DB
Arricchimento semantico della base di dati
Il controllo diventa parte della transazione stessa, se il
controllo fallisce l’intera transazione fallisce

Puo’ anche essere eseguito in ‘differita’ su alcuni DBMS
Basi di dati II
65
Problemi di Applicabilita’ dei
Trigger



Complessita’: Bisogna conoscere tutti gli effetti diretti
ed indiretti dell’azione del trigger (ancora peggio per
quelli che attivano altri trigger in cascata)
Rigidita’: Si potrebbe volere occasionalmente una
eccezione al trigger, ma non si puo’ evitare l’attivazione
del trigger
Debugging: Difficile da eseguire, manca un sistema di
controllo e debugging in genere

Stessi problemi dei linguaggi dichiarativi (CLIPS, OPS5)
Basi di dati II
66
Altri problemi semantici

Risoluzione di conflitti. Piu’ trigger sono attivabili allo
stesso momento. Varie politiche di gestione




L’ordine di esecuzione e’ quello di definizione
L’utente specifica un ordine per ogni trigger (e.g., after trig1)
Gestito arbitrariamente dal sistema
Trigger in Cascata. Trigger si attivano a vicenda,
ricorsivamente. Varie politiche di gestione



Non permesso
Limitazione del numero di attivazioni
Dinamico
Basi di dati II
67