Basi di dati attive
1
Sommario



Preliminari
Approcci architetturali
Linguaggi per la specifica di regole
–
–
–
–

Modello di esecuzione
–
–
–
–
2
Eventi
Condizioni
Azioni
Ulteriori caratteristiche
Esecuzione delle regole
Soluzione dei conflitti
Modalità di accoppiamento
Terminazione
Sommario



3
Regole attive in Starbust
Regole attive in SQL-99
Regole attive in Oracle
Preliminari: DBMS passivi Vs attivi
4

I DBMS tradizionali sono passivi: eseguono delle
operazioni solo su richiesta

Spesso si ha la necessità di avere capacità reattive: il
DBMS reagisce autonomamente ad alcuni eventi ed
esegue determinate operazioni

In questo ultimo caso parleremo di DBMS attivi
(ADBMS), per cui è possibile definire regole attive o
trigger
Preliminari: applicazioni dei ADBMS

Esempi di applicazioni in cui i DBMS attivi sono
utili:
–
–
–
5
controllo dei processi
gestione automatizzata del lavoro di ufficio
sistemi di controllo in ambito medico
Preliminari: esempio


Esempio: gestione automatizzata di un
magazzino in cui se la quantità di un prodotto
scende sotto 4 devo ordinare 100 item di tale
prodotto
Magazzino
DBMS tradizionale:
3
Ordine di 100 item
di prodotto x
2
6
Quantità di
prodotto disponibile?
DBMS
Prodotto
Quantità
x
5
3
Vendita di 2 item
del prodotto x
1
Preliminari: esempio (cont.)

DBMS attivo:
–
Regola attiva A: se la quantità diventa <=4 allora
ordina 100 item
Magazzino
Ordine di 100 item
di prodotto x
ADBS
Prodotto
Quantità
x
5
3
Regola attiva A
Vendita di 2 item
del prodotto x
7
Preliminari


Questo è un esempio di uso delle regole attive
per monitoraggio
Altri esempi:
–
–
–
–
–
–
8
vincoli di integrità
alerting
auditing
sicurezza
statistiche
viste
Approcci architetturali
DBMS passivi: approccio 1
Applicazioni
(modifiche)
controllo
DBMS passivo
risposta
Polling
periodico
Problema: determinare la frequenza ottima di polling
9
Approcci architetturali
DBMS passivi: approccio 2
DBMS passivo
Applicazioni estese
con codice per il
monitoraggio di
situazioni
Problema:
10
 Compromette la modularità e la riusabilità del codice
 quando cambia la condizione monitorata, cambia l’applicazione
 La logica è esterna alla base di dati
Approcci architetturali
DBMS attivi
Specifica delle situazioni
da monitorare
Interrogazioni
e modifiche
(re) azioni
DBMS attivo
Eventi esterni
11
Approcci architetturali

DBMS attivi:
–
–
–
–
12
supportano il monitoraggio di situazioni
integrazione omogenea con le altre componenti del
DBMS
semantica ben definita
efficienza
Linguaggi per la specifica di regole


una base di dati attiva è una base di dati nella
quale alcune operazioni sono automaticamente
eseguite quando si verifica una determinata
situazione
la situazione può corrispondere al fatto che:
–
–

13
si verifichino eventi specifici,
siano riscontrati particolari condizioni o particolari
stati o transizioni di stato
una regola attiva (trigger) è un costrutto
sintattico per definire la reazione del sistema
Il paradigma ECA

Il paradigma più noto per la definizione dei trigger è
quello
Evento-Condizione-Azione (ECA)

Evento:
–

Condizione:
–

se è soddisfatta, l’azione del trigger è
eseguita
Azione:
–
14
se si verifica provoca l’attivazione del trigger
sequenza di operazioni che può anche modificare la base di
dati, viene eseguita solo se la condizione è vera
Il paradigma ECA

La forma più comune di trigger è quindi:
ON evento IF condizione THEN azione
se si verifica l’evento, la condizione è valutata
2. se la condizione è soddisfatta l’azione viene
eseguita
Le regole attive hanno origine dalle regole dell’
Intelligenza Artificiale
Tali regole normalmente non hanno eventi, sono della
forma (CA):
IF condizione THEN azione
1.


15
Linguaggi per la specifica di regole

Perché è vantaggioso avere l’evento?
–
16
La condizione è costosa (in termini di efficienza) da valutare,
mentre rilevare l’accadere di un evento è molto meno
complesso

Questo problema è ancora più sentito in ambito basi di
dati in cui ho grosse moli di dati

Inoltre, posso specificare azioni diverse per eventi
diversi e stessa condizione
Che cos’è un evento?
“Un evento è qualcosa che accade, o si verifica, che è di
interesse e che può essere mappato in un istante di
tempo”





17
Modifica dei dati: inserimento, cancellazione, modifica
Accesso ai dati: interrogazione su una tabella
Operazione del DBMS: login di un utente, gestione di
transazioni e/o autorizzazioni
Eventi temporali: ogni giorno alle 12
Eventi definiti da applicazioni: data troppo grande
Eventi


Possibilità di definire regole che possono
essere attivate before o after un evento
Possibilità di combinare gli eventi (eventi
compositi):
–
–
–
18
operatori logici: and, or, ecc.
sequenza: considero un trigger se due o più eventi
accadono in un certo ordine
composizione temporale: considero un trigger
quando l’evento E2 avviene 5 sec. dopo l’evento E1
Che cos’è una condizione?
“Una condizione è un ulteriore controllo che viene
eseguito quando la regola è considerata e prima che
l’azione sia eseguita”



19
Predicati: clausola WHERE di SQL, è vantaggioso
avere predicati semplici perché sono più efficienti da
valutare
Interrogazioni: condizione vera se e solo se
l’interrogazione restituisce l’insieme vuoto
Procedure applicative: chiamata ad una procedura
Condizioni: osservazioni



20
la condizione può far riferimento a stati passati
o a variabili di sistema
passaggio di parametri tra condizione e azione
(non sempre possibile)
se la condizione non c’è si assume vera
Che cos’è una azione?
“Un’azione è una sequenza di operazioni che viene
eseguita quando la regola è considerata e la sua
condizione è vera”




21
Modifica dei dati: inserimento, cancellazione, modifica
Accesso ai dati: interrogazione su una tabella
Altri comandi: definizione di dati, controllo delle
transazioni (commit, rollback), garantire e revocare
privilegi
Procedure applicative: chiamata ad una procedura
Ulteriori caratteristiche


Comandi per le regole: permettono la
creazione, la modifica e la cancellazione di una
regola, oppure la sua abilitazione o
disabilitazione
Priorità per le regole: spesso devo scegliere
quale regola attivare fra un insieme di regole
priorità relative (fra coppie di regole); più flessibili
 priorità assolute (priorità numerica); aggiornamento
con l’evolversi dell’insieme di regole

22
Modello di esecuzione

Attività fondamentali in un ADBMS:
1. rilevare gli eventi e attivare le regole corrispondenti
2. processo reattivo: selezionare ed eseguire le regole
Possono essere eseguite concorrentemente
Possibile modello:
attività 1
While true do
seleziona eventi
attiva le regole appropriate
endWhile
23
Modello di esecuzione
selezione
valutazione
attività 2
While ci sono regole da considerare Do
(1) trova una regola R da considerare
(2) valuta la condizione di R
(3) If la condizione di R è vera Then
esegui l’azione di R endIf
endWhile
esecuzione
1)
1)
2)
24
scelta non deterministica fra le regole a priorità più alta (le altre
regole rimangono attivate)
la regola viene eliminata dall’insieme di regole da considerare
verifica condizione ed esecuzione sequenziale delle operazioni
nell’azione
Passi del processo di esecuzione
L’evento viene individuato
dal DBMS
Sourc
e
signaling
Verifica
evento
Attivita’ 1
Individuazione del corpo della
regola e relativa istanziazione
triggering
Triggered
Valutazione della
condizione
signaling
Attivita’ 2
25
scheduling
Regole
attivate
Determinaz. ordine di
esecuz. delle regole
(soluzione conflitti)
valutazione
Valutaz.
regole
esecuzione
Esecuz.
regole
Modello di esecuzione


Granularità del processo reattivo: frequenza di
attivazione del processo
Gerarchia di granularità comuni:
–
–
–
–
26
sempre, non appena un evento si verifica
dopo un comando di manipolazione dei dati completo (es. dopo
un comando SQL)
ai confini (start o commit) di una transazione (insieme di
comandi)
Momenti di attivazione specificati dall’applicazione
Esecuzione delle regole

Due modalità:
–
–


27
orientata all’istanza (instance oriented): la regola
attivata è eseguita (azione) per ogni elemento della
base di dati che attiva la regola e soddisfa la
condizione
orientata all’insieme (set oriented): la regola è
eseguita una volta per l’insieme di tali elementi
dipende dalla granularità del processo reattivo
es. Granularità “sempre”  orientata all’istanza
possono esserci differenze nel risultato
Esecuzione delle regole


Esempio:
relazione Impiegati
regola R:
–


28
azione = sostituire il valore dell’attributo Stipendio delle
tuple inserite con il valore medio + 5 di Stipendio calcolato
su tutte le tuple della relazione Impiegati
esecuzione orientata all’insieme: tutti gli impiegati
appena inseriti avranno lo stesso valore per l’attributo
Stipendio
esecuzione orientata all’istanza: gli impiegati appena
inseriti avranno valori di Stipendio diversi
Soluzione dei conflitti


Il passo (1) del processo reattivo considera
una sola regola
In realtà, più regole possono essere attivate
nello stesso momento:
–
–
l’evento attiva più regole
la granularità del processo reattivo è grossolana

–

29
molti eventi si verificano prima che la regola venga attivata
regole attivate e non selezionate al passo (1) del
processo reattivo sono ancora attivate
E’ necessario scegliere una regola fra le regole
attivate
Soluzione dei conflitti

Come scegliere una regola fra un insieme di
regole attivate?
–
–
arbitrariamente
priorità


–
–
–
30
assoluta
relativa
proprietà statistiche (e.g., momento della creazione)
proprietà dinamiche (e.g., regola attivata più di
recente)
alternativa: valutare più regole concorrentemente
Modalità di accoppiamento
(coupling modes)


Regole che stabiliscono le relazioni esistenti tra la
transazione che genera l’evento e il
processamento delle regole
Specificate per regolare relazione tra:
–
–
31
evento e condizione
condizione e azione
Modalità di accoppiamento

Possibili modalità di accoppiamento sono:
–
–
–

differita:
–
–
32
Immediata: immediatamente nella stessa
transazione
Differita: al momento del commit della
transazione corrente
Separata: in una nuova transazione
utile per vincoli di integrita’
durante l’esecuzione, una transazione potrebbe
violare un vincolo ma prima del commit potrebbe
ripristinare uno stato consistente
Modalità di accoppiamento
modalità EC (modalità CA immediata)
33
Il problema della terminazione


Il processo reattivo potrebbe non terminare
Soluzioni possibili:
–
–
–
lasciare al progettista il compito di progettare le
regole di modo che la non terminazione non si
verifichi
fissare un limite superiore che stabilisce un numero
massimo di regole che possono essere attivate
restrizioni sintattiche sulle regole per garantire la
terminazione:



34
le regole non si possono attivare a vicenda
le regole si possono attivare a vicenda ma non formano cicli
le regole possono formare cicli ma si garantisce che la condizione
di qualche regola, prima o poi, diventa falsa
Tabelle di transizione

Sono relazioni che permettono di riferire l’insieme di
tuple che sono state effettivamente






35
inserite
cancellate
modificate
Nel caso di “tuple modificate” le tabelle sono due: una
contiene i valori prima della modifica, mentre l’altra
contiene i valori successivi alla modifica
Possono essere usate nella valutazione della
condizione e/o della azione di una regola
Migliorano l’efficienza, limitando la valutazione della
condizione della regola alla tabella di transizione
Le regole attive in Starbust
36
Starbust
37

Progetto di ricerca sviluppato all’IBM

Starbust: DBMS relazionale estensibile al quale è stata
aggiunta una componente attiva

Ha influenzato molto lo standard SQL:1999

Completa integrazione della componente reattiva del
sistema con il linguaggio di interrogazione e le
transazioni
Starbust

Regola in Starbust:
CREATE RULE Nome ON Relazione
WHEN Eventi
[IF Condizione]
THEN Lista Azioni
[PRECEDES Lista Regole]
[FOLLOWS Lista Regole]

38
Nota: più di un evento può attivare una regola
Starbust - eventi e condizioni

Possibili eventi:
–
–
–
–


39
inserted
deleted
updated
updated(a1,…,an)
Condizione: condizione SQL
Nota: non c’è passaggio di parametri
Starbust - azioni

Possibili azioni:
–
–
–

Comandi di manipolazione
INSERT, DELETE, UPDATE, SELECT
Comandi di definizione
CREATE/DROP TABLE, CREATE/DROP VIEW,
DROP RULE
Comando transazionale di ROLLBACK
Clausole PRECEDES/FOLLOWS
vengono utilizzate per definire delle priorità relative
fra le regole
40
Starbust - esempio
41

Si considerino le due tabelle
Impiegati(Imp#,Stipendio,Dip#)
Dipartimenti(Dip#,Dirigente)

Si vuole imporre il seguente vincolo:
lo stipendio di un impiegato non può essere
maggiore dello stipendio del direttore del
dipartimento in cui lavora
Starbust - esempio (cont.)

Per garantire il precedente vincolo si può definire la
seguente regola attiva:
CREATE RULE stipendio_troppo_alto ON Impiegati
WHEN inserted, updated(Stipendio), updated(Dip#)
IF SELECT *
FROM Impiegati E, Impiegati M, Dipartimenti D
WHERE E.Stipendio>M.Stipendio AND
E.Dip#=D.Dip# AND D.Dirigente = M.Imp#
THEN ROLLBACK;

42
Nota: dovrei definire una regola simile su Dipartimenti
Starbust - transition table
43

insieme di tuple che sono state effettivamente
inserite, cancellate, modificate

dette anche delta table

migliorano l’efficienza e il potere espressivo
Starbust - transition table

Starbust ammette le seguenti transition table:
–
–
–

44
inserted
deleted
new-updated, old-updated
Le transition table sono usate nella valutazione
della condizione e nell’azione
Starbust - esempio

Si considerino le due tabelle
Impiegati(Imp#,Stipendio,Dip#)
Dipartimenti(Dip#,Dirigente)

45
Si vuole imporre il seguente vincolo:
lo stipendio di un impiegato non può essere
aumentato più di 100
Starbust - es. (cont.)

Per garantire il precedente vincolo si può definire la
seguente regola attiva:
CREATE RULE aumento_troppo_alto ON Impiegati
WHEN updated(Stipendio)
IF EXISTS (SELECT *
FROM old-updated ou, new-updated nu
WHERE nu.Stipendio-ou.Stipendio>100)
THEN ROLLBACK;
46
Starbust - es. (cont.)

Si supponga adesso di voler inserire nella relazione
Ben_Pagato gli impiegati che guadagnano più di 3000
CREATE RULE ins_in_bp ON Impiegati
WHEN inserted
THEN INSERT INTO Ben_Pagato
SELECT * FROM inserted
WHERE Stipendio > 3000
FOLLOWS aumento_troppo_alto ;
47
Starbust - es. (cont.)

Se lo stipendio medio degli impiegati inseriti eccede la
media dello stipendio di tutti gli impiegati di almeno
1000, assegnare a tutti gli impiegati inseriti uno
stipendio pari a 5000
CREATE RULE avg_ins ON Impiegati
WHEN inserted
IF (SELECT avg(Stipendio) FROM inserted) (SELECT avg(Stipendio) FROM Impiegati) > 1000
THEN UPDATE Impiegati
SET Stipendio = 5000
WHERE Imp# IN (SELECT Imp# FROM inserted);
48
Starbust- altri comandi




CREATE
DROP
ALTER
DEACTIVATE
–

49
la regola non puo’ piu’ essere attivata
ACTIVATE
Starbust - esecuzione regole




Granularita’ transazionale di default
possibilità di richiedere esplicitamente
l’attivazione del processo reattivo (processing
point) con il comando PROCESS RULES
esecuzione set-oriented
le regole sono eseguite alla fine delle
transazioni
–
–

50
EC deferred
CA immediate
la semantica si basa sulla nozione di
transizione di stato e di effetto netto
Starbust - transizione di stato

Una transizione di stato è la trasformazione da
uno stato ad un altro della base di dati prodotta
dall’esecuzione di una sequenza di operazioni
SQL di manipolazione dei dati (nel contesto di
una transazione)
Transazione
S0
51
S1
Starbust - effetto netto

L’effetto netto di una transizione di stato è costituito
dall’insieme delle tuple inserite, da quello delle tuple
cancellate e da quello delle tuple modificate

L’effetto netto è usato per calcolare le transition table e
per stabilire quali regole sono attivate
Se ho la transizione:
S0
52
Transazione
S1
Starbust - effetto netto

L’effetto netto sarà composto dai seguenti
insiemi:
–
–
–
53
tuple inserite: stato in S1
tuple cancellate: stato in S0
tuple modificate: stato vecchio in S0, stato nuovo in
S1
Starbust - effetto netto

Sia t tupla modificata durante una transizione

inserisco t, modifico t: considero l’inserimento di t già
modificata
modifico t, cancello t: considero la cancellazione di t
modifico t più volte: vecchio valore in S0, nuovo valore
in S1
inserisco t, cancello t: la tupla non è considerata
nell’effetto netto



54
Starbust - effetto netto
55

Una regola viene attivata se una o più operazioni dei
suoi eventi sono occorse nella transizione che
determina il passaggio dallo stato all’inizio della
transazione (S0) allo stato alla fine della transazione
(S1)

Caso particolare: processing point

Le transition table sono calcolate analogamente
Starbust - terminazione

Meccanismo di timeout:
–
–
56
sia n un numero predefinito dall’amministratore del
sistema
se più di n regole vengono attivate sequenzialmente
dal sistema, la transazione viene abortita
Starbust - tabella riassuntiva
57
Modello dei dati
Relazionale
Eventi primitivi
Operazioni sulla base di dati
Eventi compositi
Si
Passaggio di parametri
No
Condizioni su stati passati
Sì (gruppi di tuple modificate)
Net effect
Si
Modalità di accoppiamento
Deferred
Terminazione
Per timeout
Ordinamento regole
priorità relativa
Le regole attive in SQL-99
58
SQL-99 - regole attive

Creazione di una regola attiva :
CREATE TRIGGER Nome
{BEFORE | AFTER} Evento ON Relazione
[REFERENCING { OLD [ROW] [AS] Variabile |NEW [ROW] [AS] Variabile |
OLD TABLE [AS] Variabile | NEW TABLE [AS] Variabile}
[FOR EACH {ROW | STATEMENT}]
[WHEN Condizione]
Comandi SQL

Cancellazione di una regola attiva:
DROP TRIGGER Nome
59
SQL-99 - evento

Evento :
–
–
–
–
possibili eventi: INSERT, DELETE, UPDATE, UPDATE OF
Lista attributi
se si specifica UPDATE OF a1,…,an, la regola viene attivata
solo da un evento che modifica tutti e soli gli attributi a1,…,an
un solo evento può attivare una regola, quindi una sola
operazione su una sola tabella
è possibile specificare che il trigger sia attivato prima (before)
o dopo (after) l’evento


60
trigger before: la regola viene eseguita immediatamente priva
dell’esecuzione dell’operazione associata all’evento
trigger after: la regola viene eseguita dopo l’esecuzione
dell’operazione associata all’evento
SQL-99 - condizione e azione

Condizione:
–
–

predicato SQL arbitrario (clausola WHERE)
non è verificata se restituisce FALSE o UNKNOWN
Azione
– un singolo statement SQL
– una sequenza di statement
BEGIN ATOMIC
SQL statement 1, SQL statement 2,…
END

condizione e azione possono essere eseguite
–
–
FOR EACH ROW
FOR EACH STATEMENT

61
eseguito anche se il comando che attiva il trigger in realtà non ha
modificato alcuna tupla
SQL-99 - azione


62
Trigger before: definizione di dati, selezioni di
dati, chiamate di procedure, ecc ma non è
possibile effettuare operazioni che modificano
lo stato della base di dati
Trigger after: tutto quello che si può
specificare in un trigger before + operazioni di
manipolazione dei dati (INSERT, DELETE,
UPDATE)
SQL-99 - tipi di trigger
BEFORE
AFTER
63
STATEMENT
ROW
Trigger before statement:
il trigger è eseguito un’unica
volta prima dell’esecuzione del
comando che lo attiva
Trigger before row:
il trigger è eseguito prima di
modificare ogni tupla coinvolta
dall’esecuzione del comando che
attiva il trigger
Trigger after statement:
il trigger è eseguito un’unica
volta dopo l’esecuzione del
comando che lo attiva
Trigger after row:
il trigger è eseguito dopo aver
modificato ogni tupla coinvolta
dall’esecuzione del comando che
attiva il trigger
SQL-99 - tipi di trigger

Row level vs statement level:
–
–

Before vs after:
–
64
conviene usare trigger row level se l’azione del trigger dipende
dal valore della tupla modificata
conviene usare trigger statement level se l’azione del trigger è
globale per tutte le tuple modificate (fare un controllo di
autorizzazione complesso, generare un singolo audit record,
calcolare funzioni aggregate)
conviene usare trigger before se l’azione del trigger determina
se il comando verrà effettivamente eseguito (si evita di
eseguire il comando e di farne eventualmente il rollback)
oppure per derivare valori di colonne da utilizzare in un
INSERT o un UPDATE
SQL-99 - clausola REFERENCING


La clausola REFERENCING “implementa” le
transition table
a livello di tabella e di tupla
–

65
Il default è ROW
è necessario specificare gli alias se la
condizione e/o l’azione si riferiscono alla
tabella sulla quale il trigger è definito
SQL-99 - clausola REFERENCING


Quesito: Quali tuple sono visibili durante la
valutazione della condizione e l’esecuzione
dell’azione?
Risposta: dipende:
–
–
–
66
dall’evento che ha attivato il trigger
dal tipo di trigger before/after
dal tipo di esecuzione (row/statement)
SQL-99 - clausola REFERENCING

Eventi:
–
–
–
67
INSERT: le tuple inserite e la nuova tabella possono
essere accedute usando la clausola
REFERENCING NEW
DELETE: le tuple cancellate e la vecchia tabella
possono essere accedute usando la clausola
REFERENCING OLD
UPDATE: i valori precedenti e correnti delle tuple
(così come la tabella precedente e corrente)
possono essere acceduti usando le clausole
REFERENCING OLD e NEW
SQL-99 - clausola REFERENCING

Before:
–

After:
–
68
non è possibile utilizzare REFERENCING OLD
TABLE e REFERENCING NEW TABLE
è possibile utilizzare tutte le clausole
SQL-99 - clausola REFERENCING

FOR EACH ROW
–

FOR EACH STATEMENT
–
69
clausola REFERENCING su tabella o su tupla
clausola REFERENCING solo su tabella
SQL-99 - clausola REFERENCING
before statement
before row
after statement
after row
70
OLD ROW
NEW ROW
OLD TABLE
-
-
-
delete, update
delete, update
insert, update
insert, update
-
NEW TABLE
-
delete, update
insert, update
delete, update
insert, update
SQL-99 - Modalità di esecuzione


Granularità a livello di singolo statement
Due modalità di esecuzione:
–
–

Coupling mode:
–
–

–
dipende dal tipo di trigger (before/after) e dalla priorità
In SQL-99 si associano priorità in base all’ordine di creazione: un
trigger “vecchio” è eseguito prima di un trigger “giovane”
modello di esecuzione ricorsivo: se durante l’esecuzione di un
trigger se ne attiva un altro
–
71
EC immediate
CA immediate
Scelta regola
–

FOR EACH ROW
FOR EACH STATEMENT (default)
–
valori old: quelli iniziali
valori new aggiornati durante la computazione
SQL-99 - Modalità di esecuzione

Problema: come “interferiscono” i trigger con il
controllo dei vincoli?

Esempio:
CREATE TRIGGER Trigger1 AFTER UPDATE ON
Tabella1 …;
CREATE TRIGGER Trigger2 BEFORE UPDATE ON
Tabella1 …;
CREATE TRIGGER Trigger3 AFTER UPDATE ON
Tabella1 …;
ALTER TABLE Tabella1 ADD CONSTRAINT Vincolo1…;
72
SQL-99 - esempio (cont.)


Qual’è l’effetto di eseguire UPDATE su
Tabella1?
Le seguenti cose nel seguente ordine
avvengono:
–
–
–
–
–
73
Trigger2 è attivato
Esecuzione dell’operazione di UPDATE su Tabella1
Controllo Vincolo1 (il controllo dei vincoli avviene
alle fine dell’esecuzione del comando)
Trigger1 è attivato
Trigger3 è attivato (più giovane di Trigger1 )
SQL-99 - modalità di esecuzione


La valutazione avviene secondo il seguente ordine:
i trigger di diverso tipo vengono eseguiti nel seguente
ordine
–
–
trigger BEFORE STATEMENT
per ogni tupla oggetto del comando



–
–

74
trigger BEFORE ROW
comando e verifica dei vincoli di integrità
trigger AFTER ROW
verifica dei vincoli che richiedono di aver completato il
comando
trigger AFTER STATEMENT
per ogni tipologia, se esiste più di un trigger, si
considera l’ordine di creazione
SQL-99 - terminazione


Lo standard non è chiaro su questo aspetto
si assume che il sistema tenga traccia delle
varie attivazioni, tramite un grafo di attivazione
–
–


75
nodi: tabelle, modifiche su tabelle
archi: eventi, azioni
grafo costruito al momento della definizione dei
trigger
se si cerca di creare un trigger che può
generare non terminazione, la creazione non è
concessa
SQL-99 - tabella riassuntiva
76
Modello dei dati
Relazionale ad oggetti
Eventi primitivi
Operazioni sulla base di dati
Eventi compositi
No
Passaggio di parametri
No
Condizioni su stati passati
Sì (tupla,tabella)
Net effect
No
Modalità di accoppiamento
Immediata
Terminazione
Controllo sintattico
Ordinamento regole
Tipo + priorità su creazione
SQL-99 - progettazione trigger




77
Decidere il tipo di trigger (row/statement,
before/after)
identificare gli eventi
determinare se serve condizione e quale
determinare azione (per violazioni di integrità in
generale meglio riparare che impedire: limitare
al minimo azioni tipo ROLLBACK e
raise_application_error)
SQL-99 - Trigger e vincoli

I trigger sono più flessibili dei trigger, infatti permettono
di stabilire come reagire ad una violazione di vincolo

La flessibilità non sempre è un vantaggio

A volte definire dei vincoli è più vantaggioso:
–
–
–
78
migliore ottimizzazione
Meno errori di programmazione
I vincoli sono parte dello standard da lungo tempo i trigger no
SQL-99 - Esempio 1

Voglio tenere traccia in una tabella
Imp_Cancellati degli impiegati cancellati dalla
tabella Impiegati
CREATE TRIGGER Cancella_Imp
AFTER DELETE ON Impiegati
REFERENCING OLD ROW AS Old
FOR EACH ROW
INSERT INTO Imp_Cancellati
VALUES (Old.Imp#);
79
SQL-99 - Esempio 2

Supponiamo che la tabella Impiegati sia:
Impiegati(Imp#,Stipendio,Dip#, Num_casa, Num_ufficio)
e di volere che il numero di casa sia uguale, di default, a
quello dell’ufficio

non è possibile gestire una situazione di questo tipo con
il vincolo DEFAULT perché DEFAULT Nome colonna non
è un vincolo legale
si potrebbe eventualmente creare un CONSTRAINT a
livello di tabella

80
SQL-99 - Esempio 2 (cont.)
CREATE TRIGGER Default_ Num_casa
AFTER INSERT ON Impiegati
REFERENCING NEW ROW AS New
FOR EACH ROW
SET New. Num_casa=
casaORuffFun(New.Num_casa, New.Num_ufficio);
Dove: casaORuffFun(valore1,valore2) funzione t.c.
CASE WHEN valore2 IS NOT NULL THEN valore2
ELSE valore1
81
SQL-99 - Esempio 3

Supponiamo che la tabella Dipartimenti abbia un
attributo Budget e che il budget di un dipartimento non
possa essere modificato dopo le 5 pm
CREATE TRIGGER Update_Dipartimenti
AFTER UPDATE OF Budget ON Dipartimenti
REFERENCING NEW TABLE AS New
WHEN (CURRENT_TIME>TIME ’17:00:00:00’)
SELECT MAX(Budget)/0 FROM New;

82
N.B. il default è FOR EACH STATEMENT
SQL-99 - Esempio 3 (cont.)


L’azione del trigger precedente genera un
errore, quindi, poiché la modalità di esecuzione
è immediata, viene effettuato il rollback
dell’azione e dell’evento che ha attivato la
regola
quindi:
–
–
–
–
83
un aggiornamento di dipartimenti attiva la regola
dopo le 17, la condizione è vera
l’azione fallisce
l’aggiornamento viene disfatto
SQL-99 - esempio 3 (cont.)

Casi (molto) particolari:
–
Evento UPDATE Dipartimenti SET budget = v1,
nome= v2

–
Evento UPDATE Dipartimenti SET budget = NULL;



84
la regola non viene attivata (l’evento è diverso)
la regola viene attivata
se la condizione è vera, si deve calcolare una divisione
NULL/0, che è legale!
Quindi l’azione non fallisce e l’update non viene abortito
SQL-99 - Esempio 4

Si considerino le seguenti tabelle:
Primi_ministri(Nome,…)
Contribuenti(Nome_contribuente,Tasse,…)
Debito_nazionale(…,quantità,…)

85
La prima volta che viene eletto Bob, le tasse vengono
diminuite dell’1%, inoltre, ogni modifica delle tasse
influenza il debito nazionale e diminuisce la popolarità
di Bob
SQL-99 - Esempio 4 (cont.)
CREATE TRIGGER Update_Primi_Ministri
AFTER UPDATE OF Nome ON Primi_Ministri
REFERENCING OLD ROW AS Old, NEW ROW AS
New
FOR EACH ROW
WHEN (New.Nome=‘Bob’ AND New.Nome<>Old.Nome)
UPDATE Contribuenti SET Tasse=Tasse * 0.99;
86
SQL-99 - Esempio 4 (cont.)
CREATE TRIGGER Update_Contribuenti
AFTER UPDATE OF Tasse ON Contribuenti
REFERENCING OLD ROW AS Old, NEW ROW AS New
FOR EACH ROW
BEGIN ATOMIC
UPDATE Debito_Nazionale
SET Quantità = Quantità+(Old.Tasse-New.Tasse);
UPDATE Primi_Ministri
SET Popolarità = Popolarità – 0.01
END;
87
SQL-99 - Esempio 4 (cont.)

Problema: i trigger sembrerebbero attivarsi a
vicenda dando vita ad un processo reattivo
infinito
Primi_Ministri

88
Contribuenti
Debito_Nazionale
Il ciclo è solo apparente perché gli UPDATE su
Primi_Ministri sono su colonne diverse
Le regole attive in Oracle
89
Trigger in Oracle
CREATE [OR REPLACE] TRIGGER Nome
{BEFORE | AFTER|INSTEAD OF}
[{delete | insert | update [of [Colonna /,]* } | OR*]
ON Relazione
[[ REFERENCING [OLD [AS] Variabile |
NEW [AS] Variabile /,]*]
FOR EACH ROW
[WHEN (Condizione) ] ]
{Blocco PL/SQL | Chiamata di procedura}
•
90
altri comandi: ALTER TRIGGER con opzioni ENABLE e DISABLE,
DROP TRIGGER
Oracle - Eventi

Eventi
–
–
–
–
–
–
–
91

Comandi di INSERT, DELETE, UPDATE, UPDATE OF Lista
attributi, su tabella o vista
comandi di CREATE, ALTER, DROP su un oggetto dello
schema
startup o shutdown della base di dati
specifico errore o errore generico
connessione/sconnessione di un utente
è possibile specificare più di un evento può attivare una regola
(in OR)
trigger attivato before o after l’evento
Noi: consideriamo solo trigger attivati da comandi DML
Oracle - Azione

Azione
–
–
92
può essere blocco PL/SQL o chiamata di procedura
(no DDL né comandi transazionali es. ROLLBACK)
nel caso in cui gli eventi siano più di uno, nell’azione
è possibile distinguere vari comportamenti in base
all’evento mediante predicati condizionali IF
inserting, IF updating, IF deleting
Oracle - Condizione

Condizione:
–
–
–
93
predicato SQL (clausola WHERE) senza sottoquery
e funzioni user-defined
è possibile specificare la condizione solo per row
trigger (FOR EACH ROW) e coinvolge solo gli
attributi della tupla modificata
per gli statement trigger si possono effettuare
comunque controlli nel blocco PL/SQL
Oracle - Tipi di trigger

4 tipi già presenti anche in SQL-99
–

trigger INSTEAD OF
–
–
–
–
94
solo per trigger creati su tabelle
solo per trigger creati su viste
il corpo viene eseguito al posto del comando che
ha attivato il trigger
sono sempre di tipo ROW
utili per implementare modifiche di viste che non
possono essere modificate direttamente dai
comandi DML (INSERT, UPDATE, DELETE)
Oracle - Esempio



Si consideri una vista definita utilizzando una funzione
di gruppo
non è possibile eseguire un’operazione di DELETE
sulla vista, utilizzando le procedure standard del DBMS
Soluzione
–
–
–
95
si definisce un trigger di tipo INSTEAD OF con evento
DELETE on Nome_Vista
l’azione del trigger modificherà le tabelle sulle quali la vista è
definita secondo la modalità prescelta
quando si cerca di cancellare dalla vista, il trigger viene
seguito AL POSTO del comando di DELETE
Oracle - Clausola REFERENCING




96
può essere specificata solo nei row trigger
per default, la vecchia riga è :old e la nuova è
:new nel blocco, old e new nella condizione
stesso approccio se si introducono nuovi alias
regole di visibilità analoghe a SQL-99
Oracle - Restrizione




97
Una tabella è mutating se è la tabella su cui è eseguito
lo statement che attiva il trigger
trigger di tipo row non possono accedere con
SELECT né modificare con INSERT, DELETE,
UPDATE le tabelle mutating
restrizione piuttosto forte
motivazione: si vuole evitare che un trigger manipoli
dati che potrebbero essere inconsistenti e
comportamenti che dipendono dall’ordine in cui le tuple
della tabella vengono processate nell’esecuzione del
comando
Oracle - Modalità di esecuzione


Granularità a livello di singolo statement
Due modalità di esecuzione:
–
–

Coupling mode:
–
–

98
FOR EACH ROW
FOR EACH STATEMENT
EC immediate
CA immediate
esecuzione ricorsiva
Oracle - Modalità di esecuzione

Scelta regola:
–
dipende dal tipo di trigger come in SQL-99


trigger BEFORE STATEMENT
per ogni tupla oggetto del comando
–
trigger BEFORE ROW
– comando e verifica dei vincoli di integrità
– trigger AFTER ROW


–

99
verifica dei vincoli che richiedono di aver completato il comando
trigger AFTER STATEMENT
se esistono più trigger dello stesso tipo: scelta non
deterministica
si noti che poiché gli eventi di trigger INSTEAD OF
sono sempre distinti dagli eventi che attivano le altre
tipologie di trigger, quindi non devono mai essere
ordinati rispetto a trigger di altro tipo
Oracle - terminazione


Per timeout
Default:
–

10
0
32 chiamate di regole ricorsive
il numero massimo di chiamate ammesse può
essere modificato
Oracle - tabella riassuntiva
10
1
Modello dei dati
Relazionale ad oggetti
Eventi primitivi
Operazioni sulla base di dati
Eventi compositi
Si
Passaggio di parametri
No
Condizioni su stati passati
Sì (tupla)
Net effect
No
Modalità di accoppiamento
Immediata
Terminazione
Per timeout
Ordinamento regole
Tipo + non determinismo
Oracle - Esempio 1
Si vuole controllare che lo stipendio di un impiegato rientri nel range
previsto per la sua mansione
10
2
CREATE TRIGGER Controlla_Stipendio
BEFORE INSERT OR UPDATE OF Stipendio, Mansione ON Impiegati
FOR EACH ROW
WHEN (new.Mansione <> ‘presidente’)
DECLARE
minstip number; maxstip number;
BEGIN
SELECT minstip, maxstip FROM Stipendi
WHERE Mansione = :new.Mansione;
IF (:new.Stipendio < minstip OR :new.Stipendio > maxstip)
THEN raise_application_error(-20601,’stipendio fuori dal range
per l’impiegato’ || :new.Nome);
END IF;
END;
Oracle - esempio 2
Stesso trigger, con la chiamata di una procedura
ControlloStipendio, il cui corpo corrisponde al blocco
nell’azione del trigger precedente
CREATE TRIGGER Controlla_Stipendio
BEFORE INSERT OR UPDATE OF Stipendio, Mansione ON Impiegati
FOR EACH ROW
WHEN (new.Mansione <> ‘presidente’)
CALL ControlloStipendio(:new.Mansione, :new.Stipendio,
:new.Nome);
10
3
Oracle - esempio 3
Riordinare prodotti quando la disponibilità scende sotto una certa soglia
10
4
CREATE TRIGGER Riordino
AFTER UPDATE OF Disponibilità ON Magazzino
FOR EACH ROW
WHEN (new.Disponibiltà < new.QtaMinima)
DECLARE
x number;
BEGIN
SELECT COUNT(*) INTO x FROM OrdiniPendenti
WHERE CodProdotto = :new.CodProdotto;
IF (x = 0) THEN INSERT INTO OrdiniPendenti
VALUES (:new.CodProdotto, :new.QtaOrdine, SYSDATE);
END IF;
END;
Oracle - esempio 4
Mantenere colonna derivata che memorizza lo stipendio totale
dei membri di un dipartimento
10
5
CREATE TRIGGER Stipendio_Totale
AFTER DELETE OR INSERT OR UPDATE OF Deptno, Sal ON Emp
FOR EACH ROW
BEGIN /* assume che Deptno e Sal siano campi NOT NULL */
IF DELETING OR (UPDATING AND :old.Deptno != :new.Deptno)
THEN UPDATE Dept SET TotalSal = TotalSal - :old.Sal
WHERE Deptno = :old.Deptno;
END IF;
IF INSERTING OR (UPDATING AND :old.Deptno != :new.Deptno)
THEN UPDATE Dept SET TotalSal = TotalSal + :old.Sal
WHERE Deptno = :new.Deptno;
END IF;
Oracle - esempio 4 (cont.)
IF (UPDATING AND :old.Deptno = :new.Deptno AND :old.Sal != :new.Sal)
THEN UPDATE Dept SET TotalSal = TotalSal - :old.Sal + :new.Sal
WHERE Deptno = :new.Deptno;
END IF;
END;
10
6
Oracle - esempio 5

Siano prenotazioni e agenzie due tabelle legate dall’attributo
nomeAgenzia chiave esterna in prenotazioni
nomeAgenzia
prenotazioni


10
7
agenzie
trigger t1 che alla prima prenotazione crea l’agenzia, per le successive ne
aggiorna il totale di spese e di prenotazioni
trigger t1 poi esteso per controllare che ogni agenzia non abbia più di tre
prenotazioni (limite massimo consentito), nel caso solleva un’eccezione
Oracle - esempio 5 (cont.)
10
8
create or replace trigger t1
before insert on prenotazioni
for each row
declare
conta number;
begin
select count(*) into conta
from agenzie
where nomeAgenzia = :new.agenzia;
if (conta = 0)
then insert into agenzie
values (:new.agenzia,1,:new.spesa);
else update agenzie
set numPrenotazioni = numPrenotazioni + 1,
spesaTot = spesaTot + :new.spesa
where nomeAgenzia = :new.agenzia;
Oracle - esempio 5 (cont.)
create or replace package packPren as
…
troppePrenotazioni exception;
end packPren;
10
9
create or replace trigger t1
before insert on prenotazioni
for each row
declare
conta number;
prenota number;
begin
select count(*) into conta
from agenzie
where nomeAgenzia = :new.agenzia;
Oracle - esempio 5 (cont.)
if (conta = 0)
then insert into agenzie
values (:new.agenzia,1,:new.spesa);
else begin
select numPrenotazioni into prenota
from agenzie
where nomeAgenzia = :new.agenzia;
if (prenota = 3)
then raise packPren.troppePrenotazioni;
end if;
11
0
update agenzie
set numPrenotazioni = numPrenotazioni + 1,
spesaTot = spesaTot + :new.spesa
where nomeAgenzia = :new.agenzia;
end;