Basi di dati II 1

Basi di dati(modulo 2)

Prof. Giovanni GiuffridaStanza: 362 tel: 095 738 3051 e-mail: giovanni.giuffrida@dmi.unict.it

Basi di dati II 2

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 3

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 4

Programma, cont.

Linguaggi procedurali e interfaccePHP, JDBC2, QBE

Amministrazione di basi di datiControllo accessiMonitoringTuning

Basi di dati II 5

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 6

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 7

Esempio di DB semanticamente errato

Studente Voto Lode Corso32 01276545

276545 30 e lode 02787643 27 e lode 03739430 24 04

Esami

Matricola276545787643787643

CognomeRossiNeri

Bianchi

NomeMarioPieroLuca

Studenti

787643787643

32

27 e lode739430

Basi di dati II 8

Vincoli sui valori della tupla

NOT NULL implicito se l’attributo fa parte di una chiave primaria Esempio: campo matricola nella tabella Studente

DEFAULT (Costante|NULL) 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 9

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 10

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 11

Matricola

3987

3295

9345

Vigili Cognome

Rossi

Neri

Neri

Nome

Luca

Piero

Mario

Mori Gino7543

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

3295

3295

3987

3987

9345

3987

9345

9345

3295

3295

3295

Basi di dati II 12

Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca

InfrazioniCodice

34321

73321

64521

53524

Data

1/2/95

4/3/95

5/4/96

5/2/98

Vigile

3987

3295

3295

9345

Prov Numero

MI

TO

PR

PR

39548K

E39548

839548

839548

MI

TO

PR

PR

39548K

E39548

839548

839548

MI

TO

PR

39548K

E39548

839548

Basi di dati II 13

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

Vincolo di integrità referenziale

Basi di dati II 14

InfrazioniCodice34321

733216452153524

Data1/2/954/3/955/4/965/2/98

Vigile3987329532959345

Prov NumeroMITOPRPR

39548KE39548839548839548

Auto Prov NumeroMITOPR

E39548F34268839548

CognomeRossiRossiNeri

NomeMarioMarioLuca

TO E39548

TOE39548

Violazione di vincolo di integrità referenziale

Basi di dati II 15

Integrità referenziale e valori nulliImpiegati Matricola

34321

6452153524

CognomeRossiNeriVerdi

ProgettoIDEAXYZNULL

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

73032 Bianchi IDEA

Basi di dati II 16

Azioni compensative

Esempio: Viene eliminata una tupla causando cosi' una violazione

Tre possibili azioni Rifiuto dell'operazione

ON DELETE NO ACTION (la piu’ diffusa nei DBMS) Eliminazione in cascata

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 17

Rifiuto della cancellazioneImpiegati Matricola

34321

6452153524

CognomeRossiNeriVerdi

ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

53524 Neri XYZ53524 Neri XYZ

La transazione fallisce e XYZ non puo’ essere cancellato dalla relazione Progetti

Basi di dati II 18

Eliminazione in cascataImpiegati Matricola

34321

6452153524

CognomeRossiNeriVerdi

ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

53524 Neri XYZ

La transazione termina e XYZ viene cancellato anche dalla relazione Impiegati

Basi di dati II 19

Introduzione di valori nulliImpiegati Matricola

34321

6452153524

CognomeRossiNeriVerdi

ProgettoIDEAXYZNULL

73032 Bianchi IDEA

Progetti CodiceIDEA

BOHXYZ

Inizio01/200007/200109/2001

Durata362424

Costo200120150

XYZ 07/2001 24 120XYZ 07/2001 24 120XYZ 07/2001 24 120

NULL

La transazione termina e all’attributo Impiegati.Progetto viene assegnato NULL

Basi di dati II 20

Auto Prov Numero

MI

TO

PR

39548K

E39548

839548

Cognome

Rossi

Rossi

Neri

Nome

Mario

Mario

Luca

IncidentiCodice

34321

64521

Data

1/2/95

5/4/96

ProvB NumeroB

MI

TO

39548K

E39548

ProvA NumeroA

TO

PR

E39548

839548

Vincoli multipli su più attributi

Basi di dati II 21

Vincoli Interrelazionali, Sintassi

FOREIGN KEY [NomeChiaveEsterna]“(”Attributo{,Attributo} “)”REFERENCES TabellaRefON 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 22

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 23

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 ClientiON DELETE NO ACTION

FOREIGN KEY fk_AgenteOrdine (CodiceAgente) REFERENCES Agenti

ON DELETE NO ACTION

Basi di dati II 24

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 25

Definizione VIEW

Sintassi creazione VIEW:

CREATE VIEW NomeVista [“(” Attributo {,Attributo} “)”]

AS Query-Select

Basi di dati II 26

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 27

Le VIEW possono essere usate come tabelle SELECT Nome, Media

FROM Studenti, MediaVotiWHERE 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 28

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 29

Uso delle VIEW per query complesse Semplificare query complesse Esempio: non possiamo scrivere

SELECT AVG(COUNT(*))FROM AGENTIGROUP BY ZONE

AVG deve agire sui valori di un attributo.

Basi di dati II 30

Uso delle VIEW per query complesse CREATE VIEW AgPerZona (Zona,NumAg)

ASSELECT Zona,COUNT(*)

FROM AGENTIGROUP BY Zona

SELECT AVG(NumAg)FROM AgPerZona

DROP AgPerZona

Basi di dati II 31

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 32

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 agente1) 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 33

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 34

Aggiornamento delle VIEW, cont.

… utile nel caso di accesso dati controllato Esempio:

Impiegato( Nome, Cognome, Dipart, Ufficio, Stipendio) 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: CREATE VIEW Impiegato2 AS

SELECT Nome, Cognome, Dipart, UfficioFROM Impiegato

INSERT INTO Impiegato2 VALUES (…) Stipendio verra’ inizializzato a Null Se Null non e’ permesso per Stipendio l’operazione fallisce

Basi di dati II 35

Aggiornamento VIEW 2

Immaginiamo la seguente VIEW:CREATE VIEW ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’

La seguente operazione ha senso: INSERT INTO ImpiegatoRossi (…’Rossi’,…)

Basi di dati II 36

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 ImpiegatoRossiASSELECT * FROM Impiegato WHERE Cognome=‘Rossi’WITH CHECK OPTION

Adesso l’insert con ‘Bianchi’ fallisce, quella con ‘Rossi’ viene invece eseguita.

Basi di dati II 37

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, indirizzoFROM 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 38

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 39

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 40

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 41

Linguaggio procedurale

Complementano la natura dichiarativa di SQL

Costrutti tipo: FOR, WHILE, LOOP, IF, etc. Scansione iterativa di tabelle

Basi di dati II 42

Esempio linguaggio procedurale DB2

BEGIN ATOMICDECLARE cur INT;SET cur = MICROSECOND(CURRENT TIMESTAMP);IF cur > 600000 THEN

UPDATE staffSET name = CHAR(cur)WHERE id = 10;

ELSEIF cur > 300000 THENUPDATE staffSET name = CHAR(cur)WHERE id = 20;

ELSEUPDATE staffSET name = CHAR(cur)WHERE id = 30;

END IF;END

Comando IF/THEN

Basi di dati II 43

Esempio linguaggio procedurale DB2

BEGIN ATOMICFOR V1 AS

SELECT dept AS dname, max(id) AS max_idFROM staffGROUP BY deptHAVING COUNT(*) > 1ORDER BY dept

DOUPDATE staffSET id = id * -1WHERE id = v1.max_id;UPDATE staff SET dept = dept / 10WHERE dept = v1.dname AND dept < 30;

END FOR;END

Comando FOR per scansione tabella

Basi di dati II 44

Esempio linguaggio procedurale DB2

BEGIN ATOMICDECLARE c1, C2 INT DEFAULT 1;WHILE c1 < 10 DO

WHILE c2 < 20 DOSET c2 = c2 + 1;

END WHILE;SET c1 = c1 + 1;

END WHILE;UPDATE staff

SET salary = c1 ,comm = c2WHERE id = 10;

END

Comando WHILE per scansione tabella

Basi di dati II 45

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 46

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

Vantaggi delle procedure

Basi di dati II 47

Basi di dati “Attive”

Trigger: Regole basate sul paradigma Event-Condition-Action (ECA) incorporate nella base di dati Struttura tipica di una regola/trigger:

when Eventif Conditionthen Action

I DBMS attivi hanno un comportamento “reattivo” in contrasto col passivo della basi di dati tradizionali 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 48

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 49

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 50

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 51

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 52

Esempi trigger

Espressi in sintassi non-standard:

CREATE RULE ControlloStipendio ON ImpiegatiWHEN Inserted, Deleteted, Updated(Stipendio)IF (select avg(stipendio) from impiegati) > 100THEN update impiegati set stipendio=stipendio * 0.9

CREATE RULE ControlloRicchi ON ImpiegatiWHEN InsertedIF EXISTS ( select * from INSERTED

where stipendio>100)THEN Insert into ImpiegatiRicchi ( select * from INSERTED

where stipendio>100)

Basi di dati II 53

Esempio esecuzione trigger

Aldo 90

Maria 90

Luca 110

Aldo 90

Maria 90

Luca 110

Piero 150

Mario 120

Inseriamo: (Piero, 150) e (Mario, 120)

Il trigger ControlloStipendio parte:

Aldo 81

Maria 81

Luca 99

Piero 135

Mario 108

…

La tabella impiegati inizialmente:

Basi di dati II 54

Esempio esecuzione trigger,cont

Aldo 73

Maria 73

Luca 89

Piero 121

Mario 97

Il trigger ControlloStipendio parte di nuovo: Ricorsione

Inoltre, alla fine la tabella ImpiegatiRicchi conterra’:

Piero 121

Basi di dati II 55

Granularita’

Due tipi: Row-level Statement-level

Row-level Il trigger viene eseguito una volta per ogni tupla coinvolta

nell’operazione di modifica 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 56

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 Servono per accedere ai dati manipolati dal comando che ha scatenato il trigger Vengono usate in modo standard dall’SQL del trigger

Possono essere usate sia nella condizione che nell’azione Nella granularita’ statement-level il trigger usa delle tabelle di transizione:

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 57

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 58

Esempio Trigger BEFORE in DB2

CREATE TRIGGER T1NO CASCADE BEFORE INSERT ON IMPIEGATOREFERENCING NEW AS NFOR EACH ROW MODE DB2SQLWHEN ( N.STIPENDIO > (SELECT MAX(STIPENDIO)

FROM IMPIEGATO))SET N.STIPENDIO = (SELECT MAX(STIPENDIO)

FROM IMPIEGATO)

Basi di dati II 59

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_cancafter delete on progettireferencing old as ofor each row mode db2sqlbegin atomic insert into progetti_cancellati

values( o.codice, current date);end

create trigger storico_prog_canc2after delete on progettireferencing old_table as ofor each statement mode db2sqlbegin atomic insert into progetti_cancellati select codice, current date from o;end

Basi di dati II 60

Esecuzione del trigger

Immediata Non appena il comando viene eseguito Nel mezzo della transazione Fa parte della stessa transazione

Differita 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 61

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 62

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 63

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 64

Cancellazione automatica

Per cancellare la riga dei Totali relativa ad un agente che viene licenziato

CREATE TRIGGER cancellaAgenteAFTER DELETE ON AgentiFOR EACH ROWBEGIN DELETE FROM Totali

WHERE CodiceAgente = :old.CodiceAgente; END;

Nota che per il DELETE e’ stato usato “:old”

Basi di dati II 65

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 66

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 67

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