basi di dati attive n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Basi di dati attive PowerPoint Presentation
Download Presentation
Basi di dati attive

Loading in 2 Seconds...

play fullscreen
1 / 110

Basi di dati attive - PowerPoint PPT Presentation


  • 109 Views
  • Uploaded on

Basi di dati attive. 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. Sommario.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Basi di dati attive' - guri


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
sommario
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
sommario1
Sommario
  • Regole attive in Starbust
  • Regole attive in SQL-99
  • Regole attive in Oracle
preliminari dbms passivi vs attivi
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
preliminari applicazioni dei adbms
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
preliminari esempio

Magazzino

Prodotto

Quantità

DBMS

x

5

Quantità di

prodotto disponibile?

Vendita di 2 item

del prodotto x

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

preliminari esempio cont
Preliminari: esempio (cont.)
  • DBMS attivo:
    • Regola attiva A: se la quantità diventa <=4 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

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

controllo

Applicazioni

(modifiche)

Polling

periodico

DBMS passivo

risposta

Approcci architetturali

DBMS passivi: approccio 1

Problema: determinare la frequenza ottima di polling

approcci architetturali1

DBMS passivo

Applicazioni estese

con codice per il

monitoraggio di

situazioni

Approcci architetturali

DBMS passivi: approccio 2

Problema:

  • Compromette la modularità e la riusabilità del codice
    • quando cambia la condizione monitorata, cambia l’applicazione
  • La logica è esterna alla base di dati
approcci architetturali2

Specifica delle situazioni

da monitorare

Interrogazioni

e modifiche

(re) azioni

DBMS attivo

Eventi esterni

Approcci architetturali

DBMS attivi

approcci architetturali3
Approcci architetturali
  • DBMS attivi:
    • supportano il monitoraggio di situazioni
    • integrazione omogenea con le altre componenti del DBMS
    • semantica ben definita
    • efficienza
linguaggi per la specifica di regole
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
il paradigma eca
Il paradigma ECA
  • Il paradigma più noto per la definizione dei trigger è quello

Evento-Condizione-Azione (ECA)

  • Evento:
    • se si verifica provoca l’attivazione del trigger
  • Condizione:
    • se è soddisfatta, l’azione del trigger è eseguita
  • Azione:
    • sequenza di operazioni che può anche modificare la base di dati, viene eseguita solo se la condizione è vera
il paradigma eca1
Il paradigma ECA
  • La forma più comune di trigger è quindi:

ON evento IF condizione THEN azione

    • se si verifica l’evento, la condizione è valutata
    • 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

linguaggi per la specifica di regole1
Linguaggi per la specifica di regole
  • Perché è vantaggioso avere l’evento?
    • 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
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
eventi
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 l’evento E2 avviene 5 sec. dopo l’evento E1
che cos una condizione
Che cos’è una condizione?

“Una condizioneè un ulteriore controllo che viene eseguito quando la regola è considerata e prima che l’azione sia eseguita”

  • 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
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
che cos una azione
Che cos’è una azione?

“Un’azione è 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
ulteriori caratteristiche
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
modello di esecuzione
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

modello di esecuzione1
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

  • 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
Passi del processo di esecuzione

L’evento 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 della condizione

valutazione

Valutaz.

regole

esecuzione

signaling

Attivita’ 2

Esecuz.

regole

modello di esecuzione2
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 dall’applicazione
esecuzione delle regole
Esecuzione delle regole
  • Due modalità:
    • 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 regole1
Esecuzione delle regole
  • Esempio:

relazione Impiegati

  • regola R:
    • 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
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
      • 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 conflitti1
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
modalit di accoppiamento coupling modes
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:
    • evento e condizione
    • condizione e azione
modalit di accoppiamento
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 l’esecuzione, una transazione potrebbe violare un vincolo ma prima del commit potrebbe ripristinare uno stato consistente
modalit di accoppiamento1
Modalità di accoppiamento

modalità EC (modalità CA immediata)

il problema della terminazione
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
tabelle di transizione
Tabelle di transizione
  • Sono relazioni che permettono di riferire l’insieme 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 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
starbust
Starbust
  • 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
starbust1
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
starbust eventi e condizioni
Starbust - eventi e condizioni
  • Possibili eventi:
    • inserted
    • deleted
    • updated
    • updated(a1,…,an)
  • Condizione: condizione SQL
  • Nota: non c’è passaggio di parametri
starbust azioni
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

starbust esempio
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

starbust esempio cont
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;

  • Nota: dovrei definire una regola simile su Dipartimenti
starbust transition table
Starbust - transition table
  • insieme di tuple che sono state effettivamente inserite, cancellate, modificate
  • dette anche delta table
  • migliorano l’efficienza e il potere espressivo
starbust transition table1
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 nell’azione
starbust esempio1
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

starbust es cont
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;

starbust es cont1
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 ;

starbust es cont2
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);

starbust altri comandi
Starbust- altri comandi
  • CREATE
  • DROP
  • ALTER
  • DEACTIVATE
    • la regola non puo’ piu’ essere attivata
  • ACTIVATE
starbust esecuzione regole
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
    • EC deferred
    • CA immediate
  • la semantica si basa sulla nozione di transizione di stato e di effetto netto
starbust transizione di stato
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)

S0 S1

Transazione

starbust effetto netto
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 S1

Transazione

starbust effetto netto1
Starbust - effetto netto
  • L’effetto 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
starbust effetto netto2
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
starbust effetto netto3
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 all’inizio della transazione (S0) allo stato alla fine della transazione (S1)
  • Caso particolare: processing point
  • Le transition table sono calcolate analogamente
starbust terminazione
Starbust - terminazione
  • Meccanismo di timeout:
    • 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
Starbust - tabella riassuntiva

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

Per timeout

Terminazione

Ordinamento regole

priorità relativa

sql 99 regole attive
SQL-99 - regole attive
  • Creazione di una regola attiva:

CREATE TRIGGERNome

{BEFORE | AFTER} EventoONRelazione

[REFERENCING { OLD[ROW] [AS] Variabile |NEW[ROW] [AS] Variabile |

OLDTABLE [AS] Variabile | NEWTABLE [AS] Variabile}

[FOR EACH {ROW | STATEMENT}]

[WHENCondizione]

Comandi SQL

  • Cancellazione di una regola attiva:

DROP TRIGGERNome

sql 99 evento
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
      • 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
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
sql 99 azione
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)
sql 99 tipi di trigger

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

BEFORE

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

AFTER

SQL-99 - tipi di trigger
sql 99 tipi di trigger1
SQL-99 - tipi di trigger
  • Row level vs statement level:
    • 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)
  • Before vs after:
    • 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
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 l’azione si riferiscono alla tabella sulla quale il trigger è definito
sql 99 clausola referencing1
SQL-99 - clausola REFERENCING
  • Quesito: Quali tuple sono visibili durante la valutazione della condizione e l’esecuzione dell’azione?
  • Risposta: dipende:
    • dall’evento che ha attivato il trigger
    • dal tipo di trigger before/after
    • dal tipo di esecuzione (row/statement)
sql 99 clausola referencing2
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
sql 99 clausola referencing3
SQL-99 - clausola REFERENCING
  • Before:
    • non è possibile utilizzare REFERENCING OLD TABLE e REFERENCING NEW TABLE
  • After:
    • è possibile utilizzare tutte le clausole
sql 99 clausola referencing4
SQL-99 - clausola REFERENCING
  • FOR EACH ROW
    • clausola REFERENCING su tabella o su tupla
  • FOR EACH STATEMENT
    • clausola REFERENCING solo su tabella
sql 99 modalit di esecuzione
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 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
    • valori old: quelli iniziali
    • valori new aggiornati durante la computazione
sql 99 modalit di esecuzione1
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…;

sql 99 esempio cont
SQL-99 - esempio (cont.)
  • Qual’è l’effetto di eseguire UPDATE su Tabella1?
  • Le seguenti cose nel seguente ordine avvengono:
    • 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 esecuzione2
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 l’ordine di creazione
sql 99 terminazione
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
sql 99 tabella riassuntiva
SQL-99 - tabella riassuntiva

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

Controllo sintattico

Terminazione

Ordinamento regole

Tipo + priorità su creazione

sql 99 progettazione trigger
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)
sql 99 trigger e vincoli
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
sql 99 esempio 1
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#);

sql 99 esempio 2
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
sql 99 esempio 2 cont
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

sql 99 esempio 3
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;

  • N.B. il default è FOR EACH STATEMENT
sql 99 esempio 3 cont
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:
    • un aggiornamento di dipartimenti attiva la regola
    • dopo le 17, la condizione è vera
    • l’azione fallisce
    • l’aggiornamento viene disfatto
sql 99 esempio 3 cont1
SQL-99 - esempio 3 (cont.)
  • Casi (molto) particolari:
    • Evento UPDATE Dipartimenti SET budget = v1, nome= v2
      • la regola non viene attivata (l’evento è 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 l’azione non fallisce e l’update non viene abortito
sql 99 esempio 4
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 dell’1%, inoltre, ogni modifica delle tasse influenza il debito nazionale e diminuisce la popolarità di Bob
sql 99 esempio 4 cont
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;

sql 99 esempio 4 cont1
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;

sql 99 esempio 4 cont2

Primi_Ministri

Contribuenti

Debito_Nazionale

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
trigger in oracle
Trigger in Oracle

CREATE [OR REPLACE] TRIGGERNome

{BEFORE | AFTER|INSTEAD OF}

[{delete | insert | update [of [Colonna/,]* } | OR*]

ONRelazione

[[ 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
oracle eventi
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 l’evento
  • Noi: consideriamo solo trigger attivati da comandi DML
slide92

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, nell’azione è possibile distinguere vari comportamenti in base all’evento mediante predicati condizionali IF inserting, IF updating, IF deleting
slide93

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
oracle tipi di trigger
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)
oracle esempio
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
    • 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
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
oracle restrizione
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 dall’ordine in cui le tuple della tabella vengono processate nell’esecuzione del comando
oracle modalit di esecuzione
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
oracle modalit di esecuzione1
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
oracle terminazione
Oracle - terminazione
  • Per timeout
  • Default:
    • 32 chiamate di regole ricorsive
  • il numero massimo di chiamate ammesse può essere modificato
oracle tabella riassuntiva
Oracle - tabella riassuntiva

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

Per timeout

Terminazione

Ordinamento regole

Tipo + non determinismo

oracle esempio 1
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 <> ‘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
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);

oracle esempio 3
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à < new.QtaMinima)

DECLARE

x number;

BEGIN

SELECT COUNT(*) INTO x FROM OrdiniPendenti

WHERE CodProdotto = :new.CodProdotto;

IF (x = 0) THENINSERTINTO OrdiniPendenti

VALUES (:new.CodProdotto, :new.QtaOrdine, SYSDATE);

END IF;

END;

oracle esempio 4
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;

oracle esempio 4 cont
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;

oracle esempio 5
Oracle - esempio 5
  • Siano prenotazioni e agenzie due tabelle legate dall’attributo nomeAgenzia chiave esterna in prenotazioni
  • 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

nomeAgenzia

prenotazioni

agenzie

oracle esempio 5 cont
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)

theninsert into agenzie

values (:new.agenzia,1,:new.spesa);

elseupdate agenzie

set numPrenotazioni = numPrenotazioni + 1,

spesaTot = spesaTot + :new.spesa

where nomeAgenzia = :new.agenzia;

oracle esempio 5 cont1
Oracle - esempio 5 (cont.)

create or replace package packPrenas

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;

oracle esempio 5 cont2
Oracle - esempio 5 (cont.)

if (conta = 0)

theninsert into agenzie

values (:new.agenzia,1,:new.spesa);

elsebegin

select numPrenotazioni into prenota

from agenzie

where nomeAgenzia = :new.agenzia;

if (prenota = 3)

then raise packPren.troppePrenotazioni;

endif;

update agenzie

set numPrenotazioni = numPrenotazioni + 1,

spesaTot = spesaTot + :new.spesa

where nomeAgenzia = :new.agenzia;

end;