Trigger Trigger Un trigger definisce unazione che il

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 l per imporre regole complesse legate all’attività del database l per effettuare revisioni sulle modifiche dei dati. l

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 l BEFORE INSERT istruzione l AFTER INSERT riga l AFTER INSERT istruzione l BEFORE UPDATE riga l BEFORE UPDATE istruzione l AFTER UPDATE riga l AFTER UPDATE istruzione l BEFORE DELETE riga l BEFORE DELETE istruzione l AFTER DELETE riga l AFTER DELETE istruzione l INSTEAD OF riga l INSTEAD OF istruzione l

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 <Nome. Trigger> Modo Evento {, Evento} ON <Tabella. Target> 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 Trig. Birra 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 Nome Vendite Birra Cod_bar prezzo

Esempio Birre Nome Vendite Birra Cod_bar prezzo CREATE OR REPLACE TRIGGER Trig. Birra 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 Trig. Prezzo che memorizza nella relazione Ripoff. Bars(cod_bar) il nome di ogni bar che aumenta il prezzo di una qualunque birra di più di 1 euro. Lista. Bar Cod_bar Nome_bar Indirizzo_bar Telefono_bar Vendite Birra Cod_bar prezzo Ripoff. Bar Cod_bar

Lista. Bar Cod_bar Nome_bar Indirizzo_bar Telefono_bar Vendite Birra Cod_bar prezzo Ripoff. Bar Cod_bar CREATE OR REPLACE TRIGGER Trig. Prezzo AFTER UPDATE OF prezzo ON Vendite FOR EACH ROW WHEN (new. price > old. price + 1. 00) BEGIN INSERT INTO Ripoff. Bars 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 Qta. Disp Soglia Qta. Riordino 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 Ordini. Pendenti, di una quantità uguale alla quantità riordino presente nella tabella Magazzino.

Magazzino Prodotto Qta. Disp Soglia Qta. Riordino Ordini_Pendenti Prodotto Qta Data create trigger Riordina after update of Qta. Disp on Magazzino when (new. Qta. Disp < new. Soglia) for each row declare X number; begin select count(*) into X from Ordini. Pendenti where Prodotto = new. Prodotto; if X = 0 then insert into Ordini. Pendenti values (new. Prodotto, new. Qta. Riordino, 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 Controllo. Fido Before insert on Ordini For each row Declare Da. Pagare Number; BEGIN Select sum(ammontare) into Da. Pagare Eccezione From Ordini Where cod_cliente=: new. cod_cliente); IF Da. Pagare+: 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;