Basi di dati attive

1
Basi di dati attive
2
Sommario

• Preliminari
• Approcci architetturali
• Linguaggi per la specifica di regole
• Eventi
• Condizioni
• Azioni
• Ulteriori caratteristiche
• Modello di esecuzione
• Esecuzione delle regole
• Soluzione dei conflitti
• Modalità di accoppiamento
• Terminazione

3
Sommario

• Regole attive in Starbust
• Regole attive in SQL-99
• Regole attive in Oracle

4
Preliminari DBMS passivi Vs attivi

• 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

5
Preliminari applicazioni dei ADBMS

• Esempi di applicazioni in cui i DBMS attivi sono
  utili
• controllo dei processi
• gestione automatizzata del lavoro di ufficio
• sistemi di controllo in ambito medico

6
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
• DBMS tradizionale

3
Ordine di 100 item di prodotto x
3
2
1
7
Preliminari esempio (cont.)

• DBMS attivo
• Regola attiva A se la quantità diventa lt4
  allora ordina 100 item

Magazzino
Prodotto
Quantità
ADBS
x
5
Ordine di 100 item di prodotto x
3
Regola attiva A
Vendita di 2 item del prodotto x
8
Preliminari

• Questo è un esempio di uso delle regole attive
  per monitoraggio
• Altri esempi
• vincoli di integrità
• alerting
• auditing
• sicurezza
• statistiche
• viste

9
Approcci architetturali

• DBMS passivi approccio 1

Problema determinare la frequenza ottima di
polling
10
Approcci architetturali

• DBMS passivi approccio 2

• Problema
• Compromette la modularità e la riusabilità del
  codice
• quando cambia la condizione monitorata, cambia
  lapplicazione
• La logica è esterna alla base di dati

11
Approcci architetturali

• DBMS attivi

12
Approcci architetturali

• DBMS attivi
• supportano il monitoraggio di situazioni
• integrazione omogenea con le altre componenti del
  DBMS
• semantica ben definita
• efficienza

13
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
• 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

14
Il paradigma ECA

• Il paradigma più noto per la definizione dei
  trigger è quello
• Evento-Condizione-Azione (ECA)
• Evento
• se si verifica provoca lattivazione del trigger
• Condizione
• se è soddisfatta, lazione del trigger è
  eseguita
• Azione
• sequenza di operazioni che può anche modificare
  la base di dati, viene eseguita solo se la
  condizione è vera

15
Il paradigma ECA

• La forma più comune di trigger è quindi
• ON evento IF condizione THEN azione
• se si verifica levento, la condizione è valutata
• se la condizione è soddisfatta lazione 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

16
Linguaggi per la specifica di regole

• Perché è vantaggioso avere levento?
• La condizione è costosa (in termini di
  efficienza) da valutare, mentre rilevare
  laccadere 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

17
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
• 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

18
Eventi

• Possibilità di definire regole che possono essere
  attivate before o after un evento
• Possibilità di combinare gli eventi (eventi
  compositi)
• 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 levento E2 avviene 5 sec. dopo levento E1

19
Che cosè una condizione?

• Una condizione è un ulteriore controllo che
  viene eseguito quando la regola è considerata e
  prima che lazione sia eseguita
• Predicati clausola WHERE di SQL, è vantaggioso
  avere predicati semplici perché sono più
  efficienti da valutare
• Interrogazioni condizione vera se e solo se
  linterrogazione restituisce linsieme vuoto
• Procedure applicative chiamata ad una procedura

20
Condizioni osservazioni

• 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

21
Che cosè una azione?

• Unazione è una sequenza di operazioni che viene
  eseguita quando la regola è considerata e la sua
  condizione è vera
• 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

22
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 levolversi dellinsieme di
  regole

23
Modello di esecuzione

• Attività fondamentali in un ADBMS
• rilevare gli eventi e attivare le regole
  corrispondenti
• 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

24
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 lazione di R endIf
• endWhile

esecuzione

• scelta non deterministica fra le regole a
  priorità più alta (le altre
• regole rimangono attivate)
• la regola viene eliminata dallinsieme di regole
  da considerare
• verifica condizione ed esecuzione sequenziale
  delle operazioni
• nellazione

25
Passi del processo di esecuzione
Levento viene individuato dal DBMS
Source
signaling
Individuazione del corpo della regola e relativa
istanziazione
Verifica evento
triggering
Determinaz. ordine di esecuz. delle regole
(soluzione conflitti)
Attivita 1
Triggered
scheduling
Regole attivate
valutazione
Valutazione della condizione
Valutaz. regole
esecuzione
signaling
Attivita 2
Esecuz. regole
26
Modello di esecuzione

• Granularità del processo reattivo frequenza di
  attivazione del processo
• Gerarchia di granularità comuni
• 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
  dallapplicazione

27
Esecuzione delle regole

• Due modalità
• orientata allistanza (instance oriented) la
  regola attivata è eseguita (azione) per ogni
  elemento della base di dati che attiva la regola
  e soddisfa la condizione
• orientata allinsieme (set oriented) la regola è
  eseguita una volta per linsieme di tali elementi
• dipende dalla granularità del processo reattivo
• es. Granularità sempre ? orientata
  allistanza
• possono esserci differenze nel risultato

28
Esecuzione delle regole

• Esempio
• relazione Impiegati
• regola R
• azione sostituire il valore dellattributo
  Stipendio delle tuple inserite con il valore
  medio 5 di Stipendio calcolato su tutte le
  tuple della relazione Impiegati
• esecuzione orientata allinsieme tutti gli
  impiegati appena inseriti avranno lo stesso
  valore per lattributo Stipendio
• esecuzione orientata allistanza gli impiegati
  appena inseriti avranno valori di Stipendio
  diversi

29
Soluzione dei conflitti

• Il passo (1) del processo reattivo considera una
  sola regola
• In realtà, più regole possono essere attivate
  nello stesso momento
• levento attiva più regole
• la granularità del processo reattivo è grossolana
• 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

30
Soluzione dei conflitti

• Come scegliere una regola fra un insieme di
  regole attivate?
• arbitrariamente
• priorità
• assoluta
• relativa
• proprietà statistiche (e.g., momento della
  creazione)
• proprietà dinamiche (e.g., regola attivata più di
  recente)
• alternativa valutare più regole concorrentemente

31
Modalità di accoppiamento (coupling modes)

• Regole che stabiliscono le relazioni esistenti
  tra la transazione che genera levento e il
  processamento delle regole
• Specificate per regolare relazione tra
• evento e condizione
• condizione e azione

32
Modalità di accoppiamento

• Possibili modalità di accoppiamento sono
• Immediata immediatamente nella stessa
  transazione
• Differita al momento del commit della
  transazione corrente
• Separata in una nuova transazione
• differita
• utile per vincoli di integrita
• durante lesecuzione, una transazione potrebbe
  violare un vincolo ma prima del commit potrebbe
  ripristinare uno stato consistente

33
Modalità di accoppiamento
modalità EC (modalità CA immediata)
34
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
• 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

35
Tabelle di transizione

• Sono relazioni che permettono di riferire
  linsieme di tuple che sono state effettivamente
• inserite
• cancellate
• modificate
• Nel caso di tuple modificate le tabelle sono
  due una contiene i valori prima della modifica,
  mentre laltra contiene i valori successivi alla
  modifica
• Possono essere usate nella valutazione della
  condizione e/o della azione di una regola
• Migliorano lefficienza, limitando la valutazione
  della condizione della regola alla tabella di
  transizione

36
Le regole attive in Starbust
37
Starbust

• Progetto di ricerca sviluppato allIBM
• Starbust DBMS relazionale estensibile al quale è
  stata aggiunta una componente attiva
• Ha influenzato molto lo standard SQL1999
• Completa integrazione della componente reattiva
  del sistema con il linguaggio di interrogazione e
  le transazioni

38
Starbust

• Regola in Starbust
• CREATE RULE Nome ON Relazione
• WHEN Eventi
• IF Condizione
• THEN Lista Azioni
• PRECEDES Lista Regole
• FOLLOWS Lista Regole
• Nota più di un evento può attivare una regola

39
Starbust - eventi e condizioni

• Possibili eventi
• inserted
• deleted
• updated
• updated(a1,,an)
• Condizione condizione SQL
• Nota non cè passaggio di parametri

40
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

41
Starbust - esempio

• 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

42
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.StipendiogtM.Stipendio AND
  E.DipD.Dip AND D.Dirigente M.Imp
• THEN ROLLBACK
• Nota dovrei definire una regola simile su
  Dipartimenti

43
Starbust - transition table

• insieme di tuple che sono state effettivamente
  inserite, cancellate, modificate
• dette anche delta table
• migliorano lefficienza e il potere espressivo

44
Starbust - transition table

• Starbust ammette le seguenti transition table
• inserted
• deleted
• new-updated, old-updated
• Le transition table sono usate nella valutazione
  della condizione e nellazione

45
Starbust - esempio

• 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
  aumentato più di 100

46
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.Stipendiogt100)
• THEN ROLLBACK

47
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 gt 3000
• FOLLOWS aumento_troppo_alto

48
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) gt
  1000
• THEN UPDATE Impiegati
• SET Stipendio 5000
• WHERE Imp IN (SELECT Imp FROM inserted)

49
Starbust- altri comandi

• CREATE
• DROP
• ALTER
• DEACTIVATE
• la regola non puo piu essere attivata
• ACTIVATE

50
Starbust - esecuzione regole

• Granularita transazionale di default
• possibilità di richiedere esplicitamente
  lattivazione del processo reattivo (processing
  point) con il comando PROCESS RULES
• esecuzione set-oriented
• le regole sono eseguite alla fine delle
  transazioni
• EC deferred
• CA immediate
• la semantica si basa sulla nozione di transizione
  di stato e di effetto netto

51
Starbust - transizione di stato

• Una transizione di stato è la trasformazione da
  uno stato ad un altro della base di dati prodotta
  dallesecuzione di una sequenza di operazioni SQL
  di manipolazione dei dati (nel contesto di una
  transazione)
• S0 S1

Transazione
52
Starbust - effetto netto

• Leffetto netto di una transizione di stato è
  costituito dallinsieme delle tuple inserite, da
  quello delle tuple cancellate e da quello delle
  tuple modificate
• Leffetto netto è usato per calcolare le
  transition table e per stabilire quali regole
  sono attivate
• Se ho la transizione
• S0 S1

Transazione
53
Starbust - effetto netto

• Leffetto netto sarà composto dai seguenti
  insiemi
• tuple inserite stato in S1
• tuple cancellate stato in S0
• tuple modificate stato vecchio in S0, stato
  nuovo in S1

54
Starbust - effetto netto

• Sia t tupla modificata durante una transizione
• inserisco t, modifico t considero linserimento
  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 nelleffetto netto

55
Starbust - effetto netto

• Una regola viene attivata se una o più operazioni
  dei suoi eventi sono occorse nella transizione
  che determina il passaggio dallo stato allinizio
  della transazione (S0) allo stato alla fine della
  transazione (S1)
• Caso particolare processing point
• Le transition table sono calcolate analogamente

56
Starbust - terminazione

• Meccanismo di timeout
• sia n un numero predefinito dallamministratore
  del sistema
• se più di n regole vengono attivate
  sequenzialmente dal sistema, la transazione viene
  abortita

57
Starbust - tabella riassuntiva
Relazionale
Modello dei dati
Operazioni sulla base di dati
Eventi primitivi
Si
Eventi compositi
No
Passaggio di parametri
Sì (gruppi di tuple modificate)
Condizioni su stati passati
Si
Net effect
Deferred
Modalità di accoppiamento
Per timeout
Terminazione
Ordinamento regole
priorità relativa
58
Le regole attive in SQL-99
59
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

60
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) levento
• trigger before la regola viene eseguita
  immediatamente priva dellesecuzione
  delloperazione associata allevento
• trigger after la regola viene eseguita dopo
  lesecuzione delloperazione associata allevento

61
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
• eseguito anche se il comando che attiva il
  trigger in realtà non ha modificato alcuna tupla

62
SQL-99 - azione

• 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)

63
SQL-99 - tipi di trigger
64
SQL-99 - tipi di trigger

• Row level vs statement level
• conviene usare trigger row level se lazione del
  trigger dipende dal valore della tupla modificata
• conviene usare trigger statement level se
  lazione del trigger è globale per tutte le tuple
  modificate (fare un controllo di autorizzazione
  complesso, generare un singolo audit record,
  calcolare funzioni aggregate)
• Before vs after
• conviene usare trigger before se lazione 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

65
SQL-99 - clausola REFERENCING

• La clausola REFERENCING implementa le
  transition table
• a livello di tabella e di tupla
• Il default è ROW
• è necessario specificare gli alias se la
  condizione e/o lazione si riferiscono alla
  tabella sulla quale il trigger è definito

66
SQL-99 - clausola REFERENCING

• Quesito Quali tuple sono visibili durante la
  valutazione della condizione e lesecuzione
  dellazione?
• Risposta dipende
• dallevento che ha attivato il trigger
• dal tipo di trigger before/after
• dal tipo di esecuzione (row/statement)

67
SQL-99 - clausola REFERENCING

• Eventi
• 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

68
SQL-99 - clausola REFERENCING

• Before
• non è possibile utilizzare REFERENCING OLD TABLE
  e REFERENCING NEW TABLE
• After
• è possibile utilizzare tutte le clausole

69
SQL-99 - clausola REFERENCING

• FOR EACH ROW
• clausola REFERENCING su tabella o su tupla
• FOR EACH STATEMENT
• clausola REFERENCING solo su tabella

70
SQL-99 - clausola REFERENCING
OLD ROW NEW ROW OLD TABLE NEW TABLE
before statement - - -
before row delete, update insert, update -
after statement - - delete, update insert, update
after row delete, update insert, update delete, update insert, update
71
SQL-99 - Modalità di esecuzione

• Granularità a livello di singolo statement
• Due modalità di esecuzione
• FOR EACH ROW
• FOR EACH STATEMENT (default)
• Coupling mode
• EC immediate
• CA immediate
• Scelta regola
• dipende dal tipo di trigger (before/after) e
  dalla priorità
• In SQL-99 si associano priorità in base
  allordine di creazione un trigger vecchio è
  eseguito prima di un trigger giovane
• modello di esecuzione ricorsivo se durante
  lesecuzione di un trigger se ne attiva un altro
• valori old quelli iniziali
• valori new aggiornati durante la computazione

72
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

73
SQL-99 - esempio (cont.)

• Qualè leffetto di eseguire UPDATE su Tabella1?
• Le seguenti cose nel seguente ordine avvengono
• Trigger2 è attivato
• Esecuzione delloperazione di UPDATE su Tabella1
• Controllo Vincolo1 (il controllo dei vincoli
  avviene alle fine dellesecuzione del comando)
• Trigger1 è attivato
• Trigger3 è attivato (più giovane di Trigger1 )

74
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
• 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 lordine di creazione

75
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
• 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

76
SQL-99 - tabella riassuntiva
Relazionale ad oggetti
Modello dei dati
Operazioni sulla base di dati
Eventi primitivi
No
Eventi compositi
No
Passaggio di parametri
Sì (tupla,tabella)
Condizioni su stati passati
No
Net effect
Immediata
Modalità di accoppiamento
Controllo sintattico
Terminazione
Ordinamento regole
Tipo priorità su creazione
77
SQL-99 - progettazione trigger

• 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)

78
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
• migliore ottimizzazione
• Meno errori di programmazione
• I vincoli sono parte dello standard da lungo
  tempo i trigger no

79
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)

80
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 dellufficio
• 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

81
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

82
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_TIMEgtTIME 17000000)
• SELECT MAX(Budget)/0 FROM New
• N.B. il default è FOR EACH STATEMENT

83
SQL-99 - Esempio 3 (cont.)

• Lazione del trigger precedente genera un errore,
  quindi, poiché la modalità di esecuzione è
  immediata, viene effettuato il rollback
  dellazione e dellevento che ha attivato la
  regola
• quindi
• un aggiornamento di dipartimenti attiva la regola
• dopo le 17, la condizione è vera
• lazione fallisce
• laggiornamento viene disfatto

84
SQL-99 - esempio 3 (cont.)

• Casi (molto) particolari
• Evento UPDATE Dipartimenti SET budget v1, nome
  v2
• la regola non viene attivata (levento è diverso)
• Evento UPDATE Dipartimenti SET budget NULL
• la regola viene attivata
• se la condizione è vera, si deve calcolare una
  divisione NULL/0, che è legale!
• Quindi lazione non fallisce e lupdate non viene
  abortito

85
SQL-99 - Esempio 4

• Si considerino le seguenti tabelle
• Primi_ministri(Nome,)
• Contribuenti(Nome_contribuente,Tasse,)
• Debito_nazionale(,quantità,)
• La prima volta che viene eletto Bob, le tasse
  vengono diminuite dell1, inoltre, ogni modifica
  delle tasse influenza il debito nazionale e
  diminuisce la popolarità di Bob

86
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.NomeBob AND New.NomeltgtOld.Nome)
• UPDATE Contribuenti SET TasseTasse 0.99

87
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

88
SQL-99 - Esempio 4 (cont.)

• Problema i trigger sembrerebbero attivarsi a
  vicenda dando vita ad un processo reattivo
  infinito
• Il ciclo è solo apparente perché gli UPDATE su
  Primi_Ministri sono su colonne diverse

89
Le regole attive in Oracle
90
Trigger in Oracle

• CREATE OR REPLACE TRIGGER Nome
• BEFORE AFTERINSTEAD 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
• altri comandi ALTER TRIGGER con opzioni ENABLE e
  DISABLE, DROP TRIGGER

91
Oracle - Eventi

• Eventi
• 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 levento
• Noi consideriamo solo trigger attivati da
  comandi DML

92

Oracle - Azione

• Azione
• 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,
  nellazione è possibile distinguere vari
  comportamenti in base allevento mediante
  predicati condizionali IF inserting, IF updating,
  IF deleting

93

Oracle - Condizione

• Condizione
• 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

94
Oracle - Tipi di trigger

• 4 tipi già presenti anche in SQL-99
• solo per trigger creati su tabelle
• trigger INSTEAD OF
• 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)

95
Oracle - Esempio

• Si consideri una vista definita utilizzando una
  funzione di gruppo
• non è possibile eseguire unoperazione di DELETE
  sulla vista, utilizzando le procedure standard
  del DBMS
• Soluzione
• si definisce un trigger di tipo INSTEAD OF con
  evento DELETE on Nome_Vista
• lazione 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

96
Oracle - Clausola REFERENCING

• 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

97
Oracle - Restrizione

• 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 dallordine in cui
  le tuple della tabella vengono processate
  nellesecuzione del comando

98
Oracle - Modalità di esecuzione

• Granularità a livello di singolo statement
• Due modalità di esecuzione
• FOR EACH ROW
• FOR EACH STATEMENT
• Coupling mode
• EC immediate
• CA immediate
• esecuzione ricorsiva

99
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
• 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

100
Oracle - terminazione

• Per timeout
• Default
• 32 chiamate di regole ricorsive
• il numero massimo di chiamate ammesse può essere
  modificato

101
Oracle - tabella riassuntiva
Relazionale ad oggetti
Modello dei dati
Operazioni sulla base di dati
Eventi primitivi
Si
Eventi compositi
No
Passaggio di parametri
Sì (tupla)
Condizioni su stati passati
No
Net effect
Immediata
Modalità di accoppiamento
Per timeout
Terminazione
Ordinamento regole
Tipo non determinismo
102
Oracle - Esempio 1

• Si vuole controllare che lo stipendio di un
  impiegato rientri nel range previsto per la sua
  mansione
• CREATE TRIGGER Controlla_Stipendio
• BEFORE INSERT OR UPDATE OF Stipendio, Mansione ON
  Impiegati
• FOR EACH ROW
• WHEN (new.Mansione ltgt presidente)
• DECLARE
• minstip number maxstip number
• BEGIN
• SELECT minstip, maxstip FROM Stipendi
• WHERE Mansione new.Mansione
• IF (new.Stipendio lt minstip OR new.Stipendio gt
  maxstip)
• THEN raise_application_error(-20601,stipendio
  fuori dal range
• per limpiegato new.Nome)
• END IF
• END

103
Oracle - esempio 2

• Stesso trigger, con la chiamata di una procedura
  ControlloStipendio, il cui corpo corrisponde al
  blocco nellazione del trigger precedente
• CREATE TRIGGER Controlla_Stipendio
• BEFORE INSERT OR UPDATE OF Stipendio, Mansione ON
  Impiegati
• FOR EACH ROW
• WHEN (new.Mansione ltgt presidente)
• CALL ControlloStipendio(new.Mansione,
  new.Stipendio, new.Nome)

104
Oracle - esempio 3

• Riordinare prodotti quando la disponibilità
  scende sotto una certa soglia
• CREATE TRIGGER Riordino
• AFTER UPDATE OF Disponibilità ON Magazzino
• FOR EACH ROW
• WHEN (new.Disponibiltà lt 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

105
Oracle - esempio 4

• Mantenere colonna derivata che memorizza lo
  stipendio totale dei membri di un dipartimento
• 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

106
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

107
Oracle - esempio 5

• Siano prenotazioni e agenzie due tabelle legate
  dallattributo nomeAgenzia chiave esterna in
  prenotazioni
• trigger t1 che alla prima prenotazione crea
  lagenzia, 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
  uneccezione

nomeAgenzia
prenotazioni
agenzie
108
Oracle - esempio 5 (cont.)

• 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

109
Oracle - esempio 5 (cont.)

• create or replace package packPren as
• troppePrenotazioni exception
• end packPren
• 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

110
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
• update agenzie
• set numPrenotazioni numPrenotazioni
  1,
• spesaTot spesaTot new.spesa
• where nomeAgenzia new.agenzia
• end