Che materia stai cercando?

Appunti Basi di dati, prof. Pietro Pala

Appunti sulla parte più difficile del corso: Trigger, Viste, Proprietà Acide basati su appunti personali del publisher presi alle lezioni del prof. Pala dell’università degli Studi di Firenze - Unifi, facoltà di Ingegneria. Scarica il file in formato PDF!

Esame di Basi di dati docente Prof. P. Pala

Anteprima

ESTRATTO DOCUMENTO

6

Trigger a livello di schema e database

– sono trigger che si innescano su operazioni eseguite a livello di schema, come create table, alter table,

drop table, audit, rename, truncate e revoke.

– I trigger a livello di schema offrono principalmente due capacità:

• impedire l’esecuzione di operazioni DDL

• fornire ulteriore controllo della sicurezza quando si verificano operazioni DDL.

I Trigger SQL:2003

Un Trigger è una specifica per una determinata azione che deve essere eseguita ogniqualvolta avviene

un’azione su un determinato oggetto.

Definizioni

– triggered action (azione d’innesco), può essere sia una dichiarazione di una procedura SQL oppure una

lista di dichiarazioni.

– target table è una tabella della base di dati persistente, detta anche (tabella obiettivo) del trigger.

– Event trigger, può essere un inserimento, una cancellazione o la modifica di un insieme di righe.

A seconda del momento di azione i trigger sono divisi in AFTER TRIGGER o BEFORE TRIGGER,

a seconda della natura dell’evento innescante si dividono invece in DELETE TRIGGER, INSERT TRIGGER

oppure UPDATE TRIGGER

Per ogni trigger possono venire adoperate delle tabelle di transizione atte a memorizzare lo stato

precedente e successivo del target del trigger.

In particolare:

– Per il delete trigger è prevista una sola tabella di transizione detta old transition table

– per un insert trigger una tabella detta new transition table

– per un update trigger sono previste en trambe le tabelle prima citate

La granularità

L’azione del trigger può essere di tipo :

 statement-level trigger (eseguito solo una volta dopo l’attivazione del trigger)

 Rowlevel trigger. (eseguito una volta per ogni riga della tabella di transizione associata al trigger.

Variabili dei trigger

Vengono messe a disposizione dei trigger delle variabili speciali che rendono possibili le modifiche sulle

tabelle di transizione.

Statement-level trigger

– la variabile ha il valore della tabella di transizione

Row-level trigger

– Variabile di transizione associa ad ogni azione del trigger il valore della tupla nella tabella di transizione da

modificare; ed è una old transition variable oppure una new transition variable a seconda se si parla di

delete trigger o insert trigger, nel caso di update trigger

Definizione di trigger

1. Nome

2. Nome della tabella di target

3. Il momento d’azione (BEFORE e AFTER)

4. L’evento (insert, update, delete)

5. Se è statement-level oppure row-level trigger

6. Nomi per le tabelle di transizione o per le variabili

7. L’azione

8. Un timestamp della creazione del trigger. 7

Esecuzione

Durante l’esecuzione di un’istruzione S, possono esistere zero o più contesti d’esecuzione trigger, però al

più uno di loro può essere attivo.

Il contesto di esecuzione di un trigger (TEC) viene creato, attivato, eseguito e distrutto come descritto nelle

regole generali per S.

Trigger in MySql

Trigger di tipo insert:

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));

Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account -> FOR EACH ROW SET @sum = @sum +

NEW.amount;

Query OK, 0 rows affected (0.06 sec)

Sintassi dei trigger in MySql

CREATE

[DEFINER = { user | CURRENT_USER }]

TRIGGER nome_trigger trigger_time

trigger_event

ON tabella_target

FOR EACH ROW

trigger_stmt

Sintassi dei trigger in MySql

DEFINER, determina i privilegi da applicare quando il trigger è attivo;

TRIGGER_TIME, rappresenta il tipo di trigger: BEFORE o AFTER;

TRIGGER_EVENT, indica il tipo di primitiva che attiva il trigger:

 INSERT: il trigger è attivato quando una nuova tupla è inserita nella tabella target; ad esempio,

attraverso le primitive INSERT, LOAD DATA e REPLACE;

 UPDATE: il trigger è attivato quando una tupla della tabella target viene modificata; ad esempio,

attraverso la primitiva UPDATE;

 DELETE: il trigger è attivato quando una tupla della tabella target viene cancellata; ad esempio,

attraverso le primitive DELETE e REPLACE.

TRIGGER_STMT, è la primitiva SQL da eseguire, una volta che il trigger è attivo; se si desidera eseguire una

procedura SQL, si può utilizzare il costrutto BEGIN...END.

Variabili OLD e NEW

Per accedere alle colonne della tabella target si utilizzano gli alias OLD e NEW.

In particolare:

– OLD.col_name (read-only) si riferisce alla colonna della tabella target prima della modifica effettuata

dall’evento associato al trigger.

– NEW.col_name si riferisce alla colonna della tabella target dopo la modifica effettuata dall’evento.

In un INSERT trigger, può essere usata solo la variabile NEW;

in un DELETE trigger, può essere usata solo la variabile OLD.

In un BEFORE trigger, si può usare il comando SET NEW.column = value, per la modifica o l’inserimento di

un nuovo valore all’interno della tupla. 8

Privilegi di sistema richiesti per creare Trigger

La clausola DEFINER specifica l’account utente associato al trigger; all’atto dell’attivazione del trigger

saranno controllati i privilegi del suddetto account.

Il formato della clausola deve essere del tipo:

’user_name’@’host_name’

Privilegi in MYSQL

Al momento della creazione del trigger, l’utente che crea il trigger deve avere i privilegi di SuperUtente;

Al momento dell’attivazione del trigger, vengono controllati i privilegi dell’account utente definito nella

clausola DEFINER. Il suddetto utente deve possedere i seguenti privilegi:

– I privilegi di SuperUtente;

– Il privilegio di SELECT sulla tabella target, se sono contenuti riferimenti alle colonne della stessa

attraverso le variabili OLD e NEW);

– I privilegi di UPDATE sulla tabella target, se sono presenti primitive che modificano

– i valori delle tabelle (attraverso istruzioni del tipo: SET NEW.colname = value);

– Tutti i privilegi che sono necessari per l’esecuzione dello statement SQL.

Chiamata di procedure all’interno dei trigger

I trigger non possono invocare procedure che restituiscono dati all’utente o che utilizzano SQL dinamico;

I trigger non possono invocare procedure che esplicitamente iniziano o terminano una transazione

primitive START TRANSACTION, END TRANSACTION, COMMIT o ROLLBACK);

Gestione degli Errori

Se un BEFORE trigger fallisce, l’operazione che ha generato l’evento di attivazione, non viene eseguita;

Un AFTER trigger viene eseguito, se precedentemente sono stati eseguiti i BEFORE trigger e la primitiva di

attivazione; un errore durante un BEFORE o un AFTER trigger, genera il fallimento dell’intera procedura che

causa l’attivazione del trigger.

In un sistema transazionale, il fallimento di un trigger causa il rollback di tutte le modifiche effettuate dal

trigger; in un sistema non transazionale, invece, tutte le modifiche apportate prima dell’errore,

rimangono effettive.

Limitazione all’utilizzo dei trigger

In MySql non possono essere creati due trigger con evento e tipo evento uguali, su una stessa tabella

target.

Ad esempio:

– non possono essere definiti due BEFORE INSERT trigger o due AFTER UPDATE trigger per una stessa

tabella.

Viste

Non sempre è facilmente definibile quando sia possibile aggiornare una vista. Lo standard SQL

permette che una vista sia aggiornabile solo quando solo quando una sola riga della tabella di base

corrisponde a una riga della vista. La clausola specifica che possono essere ammessi

CHECK OPTION

aggiornamenti solo sulle righe della vista e dopo gli aggiornamenti le righe devono continuare ad

appartenere alla vista. Le opzioni L e sono usate quando la vista è definita in termini di

LOCA CASCADE

altre viste e specificano se il controllo in caso di rimozione deve essere fatto solo all’ultimo livello o

a tutti i livelli.

Viste materializzate

Una vista materializzata è un oggetto database che contiene i risultati di una query.

PLSQL

Il plsql è un linguaggio procedurale inventato da Oracle corporation 9

Introduzione a PL/SQL

● Linguaggio prcedurale di Oracle

● Altri DBMS hanno linguaggi simili

● Serve per creare

– Procedure

– Funzioni

– Package

– Trigger

Un blocco di codice contiene tre sezioni:

 Dichiarazioni (variabili, cursori, exception)

 Comandi eseguibili

 Gestione delle exception

[declare <dichiarazioni>]

Begin <comandi>

[exception <gestione eccezioni>]

end

Esempio

declare

pi constant NUMBER(9,7) := 3.1415927;

radius INTEGER(5);

perimetro NUMBER(14,2);

begin

radius := 3;

perimetro := 2*pi*radius;

insert into PERIM values (radius, perimetro);

end;

Esempio: default

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

radius INTEGER(5);

perimetro NUMBER(14,2);

begin

radius := 3;

perimetro := 2*pi*radius;

insert into PERIM values (radius, perimetro);

end;

Cursori

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

perimetro NUMBER(14,2);

cursor rag_curs is select * from VALORI_RAGGIO;

radius rag_cursore%ROWTYPE;

begin

open rag_curs;

fetch rag_curs into radius;

perimetro := 2*pi*radius.raggio;

insert into PERIM values (radius.raggio, 10

perimetro);

close rag_curs;

end;

Condizioni

if <condizione>

then

<comandi>

[elsif <condizione>

then

<comandi>]

[else

<comandi>]

end if

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

perimetro NUMBER(14,2);

cursor rag_curs is select * from VALORI_RAGGIO;

radius rag_cursore%ROWTYPE;

begin

open rag_curs;

fetch rag_curs into val_rag;

perimetro := 2*pi*radius.raggio;

if (perimetro > 30) then

insert into PERIM values (radius.raggio,

perimetro);

end if;

close rag_curs;

end;

Cicli

● Cicli semplici

● Cicli for

● Cicli while

Cicli semplici

loop

[<comandi>]

exit when <condizione>

[<comandi>]

end loop;

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

radius INTEGER(5);

perimetro NUMBER(14,2);

begin

radius := 3;

loop

perimetro := 2*pi*radius;

insert into PERIM values (radius,

perimetro); 11

radius := radius+1;

exit when radius>15;

end loop;

end;

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

perimetro NUMBER(14,2);

cursor rag_curs is select * from VALORI_RAGGIO;

radius rag_cursore%ROWTYPE;

begin

open rag_curs;

loop

fetch rag_curs into radius;

perimetro := 2*pi*radius.raggio;

insert into PERIM values (radius.raggio,

perimetro);

exit when rag_cursore%NOTFOUND;

end loop;

close rag_curs;

end;

Attributi del cursore

● %FOUND : puo` trasmettere una riga

● %NOTFOUND : non puo` trasmettere una riga

● %ISOPEN : cursore aperto

● %ROWCOUNT : numero di righe trasmesse dal cursore

Cicli for

for <variabile> in <minimo>..<massimo> loop

<comandi>

end loop;

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

radius INTEGER(5);

perimetro NUMBER(14,2);

begin

for radius in 1..100 loop

perimetro := 2*pi*radius;

insert into PERIM values (radius, perimetro);

end loop;

end;

Cicli for a cursore

declare

pi NUMBER(9,7) DEFAULT 3.1415927;

perimetro NUMBER(14,2);

cursor rag_curs is select * from VALORI_RAGGIO;

radius rag_cursore%ROWTYPE;

begin

for radius in rag_cursore

perimetro := 2*pi*radius.raggio;

insert into PERIM values (radius.raggio,

perimetro);


PAGINE

14

PESO

1.08 MB

AUTORE

kravenor

PUBBLICATO

8 mesi fa


DETTAGLI
Esame: Basi di dati
Corso di laurea: Corso di laurea in ingegneria informatica
SSD:
Docente: Pala Pietro
Università: Firenze - Unifi
A.A.: 2018-2019

I contenuti di questa pagina costituiscono rielaborazioni personali del Publisher kravenor di informazioni apprese con la frequenza delle lezioni di Basi di dati e studio autonomo di eventuali libri di riferimento in preparazione dell'esame finale o della tesi. Non devono intendersi come materiale ufficiale dell'università Firenze - Unifi o del prof Pala Pietro.

Acquista con carta o conto PayPal

Scarica il file tutte le volte che vuoi

Paga con un conto PayPal per usufruire della garanzia Soddisfatto o rimborsato

Recensioni
Ti è piaciuto questo appunto? Valutalo!

Altri appunti di Corso di laurea in ingegneria informatica

Analisi I – prova d'esame
Esercitazione
Fondamenti di Informatica I – Linguaggio C
Dispensa
Fondamenti di Informatica I – Manuale Linguaggio C
Dispensa
Appunti Analisi 1
Appunto