Trigger Trigger Un trigger definisce unazione che il

  • Slides: 38
Download presentation
Trigger

Trigger

Trigger Un trigger definisce un’azione che il database deve attivare automaticamente quando si verifica

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

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

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

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

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

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

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

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, 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

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

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

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 Trig. Birra che inserisce un nome di birra in una

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

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

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

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. prezzo > old. prezzo + 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

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

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

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>

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,

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

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

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;

Magazzino Prodotto Qta. Disp Soglia Qta. Riordino Ordini_Pendenti Prodotto Qta Data Creare un trigger

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

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

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

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,

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

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

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

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

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

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à

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.

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. Data. Azione, : old. Articolo, : old. Quantita, : old. Tipo. Quantita, : 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

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. Maiusc. Persona : = UPPER(: new. Persona); end;

Trigger: attivazione e disattivazione Abilita il trigger Biblioteca_Bef_Del alter trigger Biblioteca_Bef_Del enable; Abilita tutti

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