Trigger

1. Trigger

2. 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.

3. 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.

4. 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.

5. 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.

6. 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).

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

8. 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.

9. 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

10. Trigger, struttura • L’istruzione Create seguita dal nome assegnato al trigger • Tipo di trigger, Before/After • Evento che scatena il triggerInsert/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

11. Trigger: sintassi • create trigger <NomeTrigger> • ModoEvento {, 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

12. 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.

13. 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)

14. 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 Vendite

15. Esempio Birre Vendite CREATE OR REPLACE TRIGGERTrigBirra AFTER INSERT ONVendite FOR EACH ROW WHEN(new.birra NOT IN (SELECT nome FROM Birre)) BEGIN INSERT INTO BIRRE(nome) VALUES(:new.birra); END Evento Condizione Azione

16. 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 Vendite RipoffBar

17. ListaBar Vendite RipoffBar 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

18. Esempio Biblioteca Audit_Biblioteca Vogliamo che la tabella Audit_Biblioteca tenga traccia di ogni modifica della classificazione della tabella Biblioteca quando il valore di classificazione viene ridotto.

19. Esempio Assegna Nome trigger Create or replace Trigger Biblioteca_bef_upd_Row BEFORE UPDATE on Biblioteca FOR EACH ROW WHEN (new.Classificazione<old.Classificazione) 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 Prima dell’aggiornamento Per ogni riga modificata Quando il nuovo valore della classificazione riduce il precedente Azione

20. 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

21. Drop Trigger Un trigger si elimina mediante l’istruzione DROP Trigger <nome trigger>

22. 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.

23. 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 Audit_Biblioteca

24. 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

25. Magazzino Ordini_Pendenti 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.

26. Magazzino Ordini_Pendenti 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

27. Clienti Ordini 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

28. Clienti Ordini Create trigger ControlloFido Before insert on Ordini For each row Declare DaPagare Number; BEGIN Select sum(ammontare) into DaPagare From Ordini Where cod_cliente=:new.cod_cliente); IF DaPagare+:new.ammontare >=10000 THEN RAISE_APPLICATION_ERROR(20001, ‘Fido Superato’) ; ENDIF; END Eccezione

29. 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.

30. 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

31. 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

32. 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

33. 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:

34. Create or Replace Trigger Biblioteca_Bef_Del Before deletion on Biblioteca Declare Weekend_error EXCEPTION; Not_library _user EXCEPTION; 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; 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 Dichiarazione delle eccezioni Si assegna un nome alle eccezioni Definizione di weekend_error Definizione di not_library_user

35. 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;