Trigger
Trigger
Un trigger definisce un’azione che il database deve
attivare automaticamente quando si verifica nel database
un determinato evento.
possono essere utilizzati:
per migliorare l’integrità referenziale dichiarativa
 per imporre regole complesse legate all’attività del
database
 per effettuare revisioni sulle modifiche dei dati.

Trigger
L’esecuzione dei trigger è quindi trasparente
all’utente.
I trigger vengono eseguiti automaticamente dal
database quando specifici tipi di comandi (Eventi)
di manipolazione dei dati vengono eseguiti su
specifiche tabelle. Tali comandi comperndono i
comandi DML insert, update e delete, ma gli
ultimi DBMS prevedono anche trigger su
istruzioni DDL come Create View etc.
Anche gli aggiornamenti di specifiche colonne
possono essere utilizzati come trigger di eventi.
Privilegi
Per creare un trigger su una tabella si deve avere
la possibilità di modificare la tabella. Per cui
bisogna essere prima di tutto proprietari della
tabella, e poi bisogna avere il privilegio ALTER per
la tabella o il privilegio di sistema ALTER ANY
TABLE. Inoltre occorre avere il privilegio di
sistema CREATE TRIGGER.
Analogamente, per potere modificare un trigger di
una tabella, si deve essere proprietari della
tabella e inoltre disporre dei privilegi ALTER ANY
TRIGGER.
Tipi di Trigger
A livello di riga: vengono eseguiti una volta per
ciascuna riga modificata in una transazione; vengono
spesso utilizzati in applicazioni di revisione dei dati e
si rivelano utili per operazioni di audit dei dati e per
mantenere sincronizzati i dati distribuiti. Per creare
un trigger a livello di riga occorre specificare la
clausola FOR EACH ROW nell’istruzione create
trigger.
Tipi di Trigger
A livello di istruzione: I trigger a livello di
istruzione vengono eseguiti una sola volta per
ciascuna transazione, indipendentemente dal
numero di righe che vengono modificate. Vengono
pertanto utilizzati per attività correlate ai dati;
vengono utilizzati di solito per imporre misure
aggiuntive di sicurezza sui tipi di transazione che
possono essere eseguiti su una tabella. Sono i
trigger predefiniti nel comando create trigger
(ossia non occorre specificare che è un trigger al
livello di istruzione).
Tipi di Trigger
BEFORE e AFTER: i trigger possono essere
eseguiti prima o dopo l’utilizzo dei comandi insert,
update e delete; all’interno del trigger è possibile
fare riferimento ai vecchi e nuovi valori coinvolti
nella transazione. Occorre utilizzare la clausola
BEFORE/AFTER <tipo di evento> (insert, delete,
update).
Tipi di Trigger
INSTEAD OF: per specificare che cosa fare
invece di eseguire le azioni che hanno attivato il
trigger. Ad esempio, è possibile utilizzare un
trigger INSTEAD OF per reindirizzare le
insert in una tabella verso una tabella
differente o per aggiornare con update più
tabelle che siano parte di una vista.
Tipi di Trigger
BEFORE INSERT riga
 BEFORE INSERT istruzione
 AFTER INSERT riga
 AFTER INSERT istruzione
 BEFORE UPDATE riga
 BEFORE UPDATE istruzione
 AFTER UPDATE riga
 AFTER UPDATE istruzione
 BEFORE DELETE riga
 BEFORE DELETE istruzione
 AFTER DELETE riga
 AFTER DELETE istruzione
 INSTEAD OF riga
 INSTEAD OF istruzione

Trigger, struttura
• L’istruzione Create seguita dal nome assegnato al
trigger
• Tipo di trigger, Before/After
• Evento che scatena il trigger Insert/Delete/Update
• [For each row], Se si vuole specificare trigger al
livello di riga (altrimenti nulla per trigger al livello di
istruzione)
• Specificare a quale tabella si applica
• Condizione che si deve verificare perchè il trigger
sia eseguito
• Azione, definita dal codice da eseguire se si verifica
la condizione
Trigger: sintassi
create trigger <NomeTrigger>
Modo Evento {, Evento}
ON <TabellaTarget>
for each row
[when <Predicato SQL>]
Blocco PL/SQL
Modo Evento: before o after
Evento: insert, update, delete
for each row specifica la granularità. In assenza
di questa clausola si intende per ogni istruzione
Vecchi e nuovi valori
Poiché la maggior parte dei trigger ha a che fare con
modifiche di righe, è importante poter fare
riferimento ai valori della riga prima dell’inserimento,
cancellazione, modifica e i valori dopo tali eventi.
In particolare se, per esempio, si tratta di un trigger
BEFORE UPDATE, per valori vecchi intendiamo i valori
che sono nella tabella e che vogliamo modificare e per
nuovi quelli che vogliamo inserire al posto dei vecchi.
Se viceversa è un trigger AFTER UPDATE, per vecchi
intendiamo quelli che c’erano prima dell’update e nuovi
quelli presenti nella tabella alla fine della modifica.
Nuovi e vecchi valori
In Oracle, differentemente dagli altri DBMS, ci si
può riferire automaticamente ai vecchi valori (ossia i
valori prima dell’aggiornamento) e ai nuovi valori
(ossia quelli dopo l’aggiornamento) rispettivamente
mediante le parole chiave “old” e “new”.
Mentre nella condizione (when) le parole chiave old e
new compaiono senza nessun altro simbolo, nell’azione
le parole chiave old e new sono precedute da due
punti (:old, :new)
Esempio
Costruire il trigger TrigBirra che inserisce un nome di
birra in una tabella Birre quando viene inserita nella
tabella Vendite una birra che non era già presente nella
tabella Birre.
Birre
Nome
Vendite
Birra Cod_bar prezzo
Esempio
Birre
Nome
Vendite
Birra Cod_bar prezzo
CREATE OR REPLACE TRIGGER TrigBirra
AFTER INSERT ON Vendite
Evento
FOR EACH ROW
WHEN (new.birra NOT IN
Condizione
(SELECT nome FROM Birre))
BEGIN
INSERT INTO BIRRE(nome) VALUES(:new.birra);
Azione
END
Esempio
Creare il trigger TrigPrezzo che memorizza nella
relazione RipoffBars(cod_bar) il nome di ogni bar che
aumenta il prezzo di una qualunque birra di più di 1 euro.
ListaBar
Cod_bar Nome_bar Indirizzo_bar Telefono_bar
Vendite
Birra Cod_bar prezzo
RipoffBar
Cod_bar
ListaBar
Cod_bar Nome_bar Indirizzo_bar Telefono_bar
Vendite
Birra Cod_bar prezzo
RipoffBar
Cod_bar
CREATE OR REPLACE TRIGGER TrigPrezzo
AFTER UPDATE OF prezzo ON Vendite
FOR EACH ROW
WHEN (new.price > old.price + 1.00)
BEGIN
INSERT INTO RipoffBars VALUES(:new.cod_bar);
END
Esempio
Biblioteca
Titolo Editore Nome_cat Classificazione
Audit_Biblioteca
Titolo Editore Nome_cat Vecchia_Class Nuova_class Data_audit
Vogliamo che la tabella Audit_Biblioteca tenga traccia di
ogni modifica della classificazione della tabella Biblioteca
quando il valore di classificazione viene ridotto.
Esempio
Assegna Nome
trigger
Prima dell’aggiornamento
Create or replace Trigger Biblioteca_bef_upd_Row
BEFORE UPDATE on Biblioteca
FOR EACH ROW
Per ogni riga modificata
WHEN (new.Classificazione<old.Classificazione)
Quando il nuovo
valore della
classificazione
riduce il
precedente
BEGIN
INSERT INTO Audit_Biblioteca (Titolo, Editore,
Nome_Cat, Vecchia_Class, Nuova_Class, Data_Audit)
VALUES(:old.Titolo, :old.Editore, :old:Nomecat,
:old.Classificazione, :new.Classificazione, Sysdate)
END
Azione
Osservazione
Il funzionamento del trigger risulta
completamente trasparente all’utente che esegue
l’aggiornamento della tabella Biblioteca. Tuttavia la
transazione eseguita sulla tabella Biblioteca
dipende dal successo dell’esecuzione del trigger
Drop Trigger
Un trigger si elimina mediante l’istruzione
DROP Trigger <nome trigger>
Combinazione di tipi di trigger
E’ possibile combinare diversi trigger su diversi
comandi insert, update e delete, purchè siano tutti
allo stesso livello.
In tal caso l’evento può essere indicato per esempio
come segue
Before insert OR update
E i diversi casi vengono selezionati mediante i diversi
tipi di transazione, che sono INSERTING, DELETING
e UPDATING.
Esempio
Si consideri il trigger Biblioteca_Bef_Upd_Ins_Row,
che modifica la tabella Audit_Biblioteca, in cui, se
viene fatto un nuovo inserimento nella tabella
Biblioteca, allora vengono inseriti nella tabella
Audit_Biblioteca i valori del titolo, editore,
categorie, nuova classificazione e data
dell’inserimento; se viene effettuata una modifica,
oltre a questi valori, viene registrata nella tabella
Audit_Biblioteca anche la vecchia classificazione.
Biblioteca
Titolo Editore Nome_cat Classificazione
Audit_Biblioteca
Titolo Editore Nome_cat Vecchia_Class Nuova_class Data_audit
Esempio
Drop Trigger Biblioteca_Bef_upd_Row
Create or replace trigger Biblioteca_Bef_Upd_Ins_Row
Before Insert or Update of Classificazione on Biblioteca
For each Row
Begin
If INSERTING then
Insert into audit_Biblioteca (Titolo, Editore, Nome_cat,
Nuova_Class, Data_Audit)
Values(:new.Titolo, :new.Editore, :new.Nome_cat,
:new.Classificazione, Sysdate);
ELSE
Insert into audit_Biblioteca(Titolo, Editore, Nome_cat, Vecchia_Class
Nuova_Class, Data_Audit)
Values(:new.Titolo, :new.Editore, :new.Nome_cat, :old.Classificazione
:new.Classificazione, Sysdate);
End if;
end
Magazzino
Prodotto QtaDisp Soglia QtaRiordino
Ordini_Pendenti
Prodotto Qta Data
Creare un trigger che controlla ad ogni modifica
della quantità disponibile di un prodotto in
magazzino, se questa quantità disponibile è andata al
di sotto della soglia.
Nel qual caso, se il prodotto non è già presente negli
ordini pendenti, viene aggiunto un ordine del
prodotto nella tabella OrdiniPendenti, di una
quantità uguale alla quantità riordino presente nella
tabella Magazzino.
Magazzino
Prodotto QtaDisp Soglia QtaRiordino
Ordini_Pendenti
Prodotto Qta Data
create trigger Riordina
after update of QtaDisp on Magazzino
when (new.QtaDisp < new.Soglia)
for each row
declare X number;
begin
select count(*) into X
from OrdiniPendenti
where Prodotto = new.Prodotto;
if X = 0 then
insert into OrdiniPendenti
values (new.Prodotto, new.QtaRiordino, sysdate);
end if;
end
Clienti
Cod_cliente Nome Città sconto
Ordini
Num_ord Cod_cliente Cod_agente Prodotto Data ammontare
Supponiamo che si voglia imporre il vincolo che non si
accettano nuovi ordini da clienti con uno scoperto
maggiore di 10.000 euro. Creare un trigger affinchè si
soddisfi questo vincolo
Clienti
Cod_cliente Nome Città sconto
Ordini
Num_ord Cod_cliente Cod_agente Prodotto Data Ammontare
Create trigger ControlloFido
Before insert on Ordini
For each row
Declare DaPagare Number;
BEGIN
Select sum(ammontare) into DaPagare
Eccezione
From Ordini
Where cod_cliente=:new.cod_cliente);
IF DaPagare+:new.ammontare >=10000
THEN
RAISE_APPLICATION_ERROR(20001, ‘Fido Superato’) ;
ENDIF;
END
Trigger Attivi e Passivi
Un trigger è attivo quando, in corrispondenza di certi
eventi, modifica lo stato della base di dati.
Un trigger è passivo se serve a provocare il fallimenti
della transazione corrente sotto certe condizioni.
Solo l’ultimo esempio fra quelli visti è un trigger
passivo, mentre gli altri sono tutti attivi.
Trigger Attivi
1. Per definire le cosiddette business rules, ovvero le
azioni da eseguire per garantire la corretta
evoluzione del sistema informativo
2. Per memorizzare eventi sulla base di dati per
ragioni di controllo (auditing e logging)
3. Per propagare su altre tabelle gli effetti di certe
operazioni su tabelle
4. Per mantenere allineati eventuali dati duplicati
quando si modifica uno di essi
Trigger Passivi
1. Per definire vincoli di integrità non esprimibili nel
modello dei dati usato
2. Per fare controlli sulle operazioni ammissibili degli
utenti basati sui valori dei parametri di comandi SQL
Personalizzare le condizioni di
errore
I numeri e i messaggi di errore visualizzati dall’utente
sono impostati mediante la procedura
RAISE_APPLICATION_ERROR
Che può essere chiamata all’interno di ogni trigger
Raise_Application_Error è una procedura che prende in
input due parametri
Il numero dell’errore (che deve essere un numero
compreso tra -20001 e -20999)
Il messaggio di errore da visualizzare
Esempio
Quando un utente cerca di cancellare una riga dalla
tabella Biblioteca, il trigger Trigger Biblioteca_Bef_Del
deve verificare che l’operazione non sia fatta durante il
weekend e che l’username dell’account che effettua
l’eliminazione sia ‘LIB’.
Il codice del trigger corrispondente è il seguente:
Create or Replace Trigger Biblioteca_Bef_Del
Before deletion on Biblioteca
Declare
Weekend_error EXCEPTION;
Not_library _user EXCEPTION;
Dichiarazione delle
eccezioni
Si assegna un nome
alle eccezioni
BEGIN
IF to_char(sysdate, ‘DY’)=‘Sab’ or to_char(Sysdate, ‘DY’)=‘Dom’
THEN RAISE Weekend_error;
End if;
If upper(Utente)<> ‘LIB’ THEN RAISE not_library_error;
End if;
Definizione di
weekend_error
EXCEPTION
WHEN weekend_error THEN
Raise_Application_Error
(-20001, ‘Cancellazioni non permesse durante il weekend’);
WHEN not_library_user THEN
Raise_Application_Error
(-20002, ‘Le cancellazioni sono permesse solo agli operatori’);
END
Definizione di not_library_user
Trigger: attivazione e
disattivazione
alter trigger Biblioteca_Bef_Del enable;
alter table BIBLIOTECA enable all triggers;
alter trigger Biblioteca_Bef_Del disable;
alter table BIBLIOTECA disable all triggers;