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 comprendono 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.
Trigger a livello di riga
I 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.
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. E’ il tipo di trigger predefinito
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 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.prezzo > old.prezzo + 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,
categoria, 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
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
if UPDATING then
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 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: Esempio
Registro
Data Azione
Articolo
Quantità Importo
Persona
Audit_Registro
Data Azione
Articolo
Quantità Importo Persona
Aumento
Scrivere un trigger sulla tabella registro che ad ogni
modifica della tabella registro per cui l’aumento
dell’importo è superiore al 10%, inserisce nella
tabella Registro_audit i vecchi valori del dato
inserito, più l’aumento dell’importo
Esercizio
create trigger registro_bef_upd_row
before update on REGISTRO
for each row
when (new.Importo/old.Importo>1.1)
begin
insert into REGISTRO_AUDIT
values (:old.DataAzione, :old.Azione, :old.Articolo,
:old.Quantita, :old.TipoQuantita, :old.Tasso,
:old.Importo, :old.Persona, :new.Importo – :old_Importo);
end;
Trigger: esempio
Creare un trigger sulla tabella Registro che prima di
inserire una riga nella tabella registro, inserisca il nome
nella tabella Persone, con tutti i caratteri maiuscoli
create trigger registro_bef_upd_ins_row
before insert or update of Persona on REGISTRO
for each row
begin
:new.MaiuscPersona := UPPER(:new.Persona);
end;
Trigger: attivazione e
disattivazione
Abilita il trigger
Biblioteca_Bef_Del
alter trigger Biblioteca_Bef_Del enable;
Abilita tutti i trigger
sulla tabella Biblioteca
alter table BIBLIOTECA enable all triggers;
alter trigger Biblioteca_Bef_Del disable;
alter table BIBLIOTECA disable all triggers;
Disabilita il trigger
Biblioteca_Bef_Del
Disabilita tutti i trigger
sulla tabella Biblioteca