Nei vari articoli relativi al linguaggio SQL abbiamo esplorato tutte le potenzialità di questo linguaggio. Siamo infatti riusciti a creare un database (SQL: creazione di un database), scrivere i dati al suo interno (SQL: transazioni e manipolazione dei dati) e interrogarlo nei modi più disparati possibili per estrarre le informazioni di interesse. In tutti questi scenari il database si è comportato in modo passivo, ossia siamo stati noi ad istruirlo su cosa fare. In molti contesti questo approccio è lo standard. Le interrogazioni e gli aggiornamenti sono richiesti esplicitamente dagli utenti mediante l’applicazione, la quale incorpora la conoscenza dei processi che operano sui dati. Questo, però, può portare dei problemi. Infatti, demandare la verifica dei dati solamente alle applicazioni può introdurre incoerenze dovute a bachi del software o a regole implementate in modo differente nei vari software che si interfacciano con la basi di dati. Come si può ovviare a tutto ciò? Rendendo il database un componente attivo nell’architettura generale. Ciò si può ottenere usando i trigger.
Basi dati attive
Come detto in precedenza, per rendere una base di dati attiva è necessario incorporare al suo interno la logica che verifica la coerenza tra i dati. La reattività del database è assicurata dall’esecuzione automatica di regole attive, denominate ECA. Il nome deriva dalle componenti delle regole, ossia:
- Evento: operazione di modifica della base di dati
- Condizione: predicato sullo stato della base di dati se la condizione è vera, l’azione viene eseguita
- Azione: sequenza di istruzioni SQL o procedura applicativa
I trigger sono lo strumento nel linguaggio SQL che permette di implementare le regole attive. Essi sono composti da un insieme di istruzioni PL/SQL che vengono attivate in risposta a specifici eventi che coinvolgono tabelle o viste del database. Questi eventi possono comprendere l’inserimento, l’aggiornamento o la cancellazione di record, permettendo così di implementare regole aziendali o logiche personalizzate.
Per far sì che i trigger funzioni correttamente, tutti i database hanno un componente chiamato Rule Engine. Questo ha il compito di tracciare tutti gli eventi che occorrono nella base di dati ed eseguire le regole che sono state definite quando è appropriato. L’esecuzione delle regole, ossia dei trigger, è alternata all’esecuzione delle transazioni tradizionali e dipende dalla strategia di esecuzione definita dal DBMS.
Purtroppo, la sintassi e la logica di esecuzione dei trigger non è standard, nonostante la loro utilità sia ormai riconosciuta. Per motivi storici, ogni database ha implementato una propria sintassi sia per definire i trigger stessi, le regole di attivazione e le funzioni che possono essere usate al loro interno. Ciò comporta che, in base al database che vorrete usare, dovrete studiare la documentazione ufficiale per implementare correttamente i trigger. Inoltre, se cambiate tecnologia non potrete migrare i trigger implementati, ma li dovrete riscrivere.
Per semplicità di presentazione e di sintassi, in questo articolo useremo la sintassi di Oracle. Rispetto ad altri database, Oracle presenta una sintassi più pulita a nostro parere.
Utilizzo dei Trigger
I trigger sono estremamente versatili e possono essere utilizzati in diversi scenari, tra cui:
- Validazione dei Dati: I trigger possono essere impiegati per verificare che i dati inseriti rispettino determinate condizioni o vincoli, garantendo la coerenza dei dati nel database.
- Audit delle Modifiche: Possono essere utilizzati per tenere traccia delle modifiche apportate alle tabelle, registrando chi ha eseguito l’operazione e quando, fornendo un importante strumento di tracciamento e sicurezza.
- Aggiornamento Automatico di Campi: I trigger possono essere utilizzati per aggiornare automaticamente altri campi o tabelle in risposta a certi eventi, riducendo così la necessità di intervento manuale.
- Implementazione di Regole di Business: Possono essere sfruttati per applicare logiche aziendali personalizzate durante le operazioni di inserimento, aggiornamento o cancellazione, garantendo che le modifiche siano conformi alle politiche aziendali.
- Controllo dell’Accesso: I trigger possono essere utilizzati per limitare o controllare l’accesso a determinati dati o operazioni, contribuendo così a garantire la sicurezza del database.
Struttura di un Trigger in Oracle
Un trigger Oracle è costituito dai seguenti elementi principali:
- Nome del Trigger: Ogni trigger deve essere dotato di un nome univoco all’interno dello schema del database per poterlo identificare e gestire correttamente.
- Evento che Attiva il Trigger: Questo può includere operazioni di INSERT, UPDATE o DELETE su una specifica tabella o vista. L’evento è ciò che scatena l’esecuzione del trigger.
- Modalità di Esecuzione: I trigger possono essere suddivisi in due categorie: BEFORE e AFTER. I trigger BEFORE vengono eseguiti prima dell’azione che li ha attivati, mentre i trigger AFTER vengono eseguiti dopo l’azione.
- Tabella mutante: Il trigger è associato a una specifica tabella o vista e si applica solo a quella.
- Granularità: è possibile specificare se il trigger agirà su tutte le righe della tabella mutante o solo su una specifica riga (utilizzando il riferimento `FOR EACH ROW`).
- Corpo del Trigger: Questa è l’area in cui si definiscono le istruzioni PL/SQL da eseguire quando il trigger viene attivato. Qui è possibile implementare logiche complesse o regole aziendali specifiche.
La sintassi è la seguente:
CREATE TRIGGER TriggerName
Mode Event {OR Event }
ON TargetTable
[[ REFERENCING ReferenceName]
FOR EACH ROW
[WHEN Predicate]]
PL/SQL Block
Oltre agli elementi principali definiti in precedenza, come si può notare vi è anche il predicato WHEN. Questo può essere inserito solo per la semantica di esecuzione a livello di singolo record (ossia, FOR EACH ROW) e specifica facoltativamente una condizione di attivazione del trigger. All’interno di questo predicato è possibile accedere alle variabili di stato vecchie e nuove.
Le variabili di stato, disponibili solo quando si esegue il trigger con una granularità di tupla, sono due:
- NEW: contiene i valori aggiornati dall’evento del record. Questa variabile è disponibile solo per aggiornamenti e inserimenti.
- OLD: contiene i valori pre-evento di modifica. Questa variabile è disponibile solo per aggiornamenti e cancellazioni.
Peculiarità dei trigger di Oracle
Prima di passare ad un paio di esempi pratici, bisogna soffermarsi su alcune caratteristiche particolari di Oracle.
In primo luogo, l’algoritmo di esecuzione con cui Oracle intervalla l’esecuzione dei trigger con l’istruzione scatenante.
- Vengono eseguiti i trigger di tipo before a livello di istruzione
- Per ogni tupla nella tabella target (TargetTable) interessata dall’istruzione di trigger
- Vengono eseguiti i trigger di tipo before a livello di tupla
- Viene eseguita l’istruzione di attivazione in contemporanea con il controllo dei vincoli di integrità definiti a livello di tupla
- Vengono eseguiti i trigger di tipo after a livello di tupla
- Vengono controllati i vincoli di integrità sulle tabelle
- Vengono eseguiti i trigger di tipo after a livello di istruzione
Poiché l’esecuzione di un trigger può attivare altri trigger in cascata, ciò può portare alla non terminazione dell’esecuzione del trigger. Per questo motivo la lunghezza massima per l’esecuzione di trigger in cascata è di default 32. Si può cambiare, ma è caldamente sconsigliato.
Infine, la tabella mutante (o tabella Target) non è accessibile nei trigger a livello di riga, ma solo nei trigger con granularità a livello di istruzione. In altri database invece ciò è possibile.
Esempi Pratici di Trigger
Per comprendere meglio l’utilizzo dei trigger vediamo due esempi molto semplici.
Esempio 1: Riordino dei prodotti
Supponiamo di avere le seguenti tabelle che rappresentano un inventario e una lista di ordini da inviare.
INVENTORY (Part#, QtyOnHand, ThresholdQty, ReorderQty)
PENDING_ORDERS(Part#, OrderDate, OrderedQty)
Vogliamo gestire in automatico il riordino di un prodotto quando la sua quantità in magazzino scende al di sotto di una determinata soglia. Pertanto, gli eventi coinvolti in questa operazione saranno sia l’aggiornamento della quantità disponibile per un prodotto, che l’inserimento di un nuovo prodotto. Non appena avviene la modifica, per ogni riga della tabella Inventory, inseriremo una nuova tupla nella tabella PENDING_ORDERS con le informazioni necessarie. L’inserimento avverrà se e solo se non è già presente un ordine per quel prodotto.
Tutta questa descrizione viene tradotta in SQL come riportato di seguito:
CREATE TRIGGER Reorder
AFTER UPDATE OF QtyOnHand OR INSERT ON Inventory
FOR EACH ROW
WHEN (NEW.QtyOnHand < NEW. ThresholdQty)
DECLARE
N number;
BEGIN
select count(*) into N
from PendingOrders
where Part# = :NEW.Part#;
If (N==0) then
insert into PENDING_ORDERS(Part#,OrderedQty,OrderDate)
values (:NEW.Part#, :NEW.ReorderQty, SYSDATE);
end if;
END;
Potete notare che per salvare un valore abbiamo usato la clausola DECLARE per definire una variabile. Inoltre, è pratica molto comune per verificare che non ci siano già record con determinate caratteristiche (nel nostro caso ordini dello stesso prodotto) si usa una query di conteggio.
Esempio 2: Salario minimo
Supponiamo ora di voler implementare una verifica sul salario minimo degli impiegati di un’azienda. Per politiche aziendali, ogni dipendente non può ricevere un salario inferiore a 1000 €. Gli eventi di attivazione saranno inserimento e aggiornamento del campo salary sulla tabella employees, mentre la granularità dell’evento sarà di tipo tupla (FOR EACH ROW). All’interno del corpo del trigger verificheremo che il campo salary rispetti la soglia definita e in caso contrario emetteremo un errore. Per far ciò useremo la funzione di Oracle RAISE_APPLICATION_ERROR. L’errore causerà l’interruzione del trigger e il rollback di tutte le operazioni fatte dal trigger stesso e della transazione che lo ha scatenato. L’SQL risultante sarà il seguente:
CREATE OR REPLACE TRIGGER check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 1000 THEN
RAISE_APPLICATION_ERROR(-20001, The salary should be at least 1000.');
END IF;
END;
Esempio 3: Numero massimo di eventi
Supponiamo ora di voler gestire un calendario di eventi di un’organizzazione. Per motivi logistici e organizzativi non possono essere pianificati più di 10 eventi in ogni data. Il database è costituito dalle seguenti tabelle.
EVENT(CodE, Name, Category, Cost, Duration)
CALENDAR (CodE, Date, StartTime)
Il trigger dovrà operare in fase di inserimento di un nuovo record nella tabella CALENDAR o di un aggiornamento della data dell’evento. Poiché sarà necessario contare il numero di eventi a calendario per ogni data, dovremo accedere alla tabella mutante. La granularità di esecuzione sarà quindi a livello di istruzione. Come nell’esempio precedente useremo la funzione RAISE_APPLICATION_ERROR per emettere un errore se esiste almeno una data in cui ci sono più di 10 eventi. Il trigger risultante sarà il seguente.
CREATE TRIGGER check_n_events
AFTER INSERT OR UPDATE of Data ON CALENDARIO_EVENTI
DECLARE
X Number;
BEGIN
SELECT COUNT(*) INTO X
FROM CALENDAR
WHERE Date IN
(SELECT Date
FROM CALENDAR
GROUP BY Date
HAVING COUNT(*) > 10);
IF (X<>0) THEN
raise_application_error (XXX, “Too much events”);
END IF;
END;
Conclusioni
I trigger in Oracle sono strumenti essenziali per automatizzare operazioni e implementare logiche personalizzate all’interno di un database. Tuttavia, è fondamentale utilizzarli con attenzione e comprenderne appieno l’impatto sulle prestazioni e sulle operazioni del database. Con una progettazione e implementazione oculata, i trigger possono notevolmente migliorare l’efficienza e l’affidabilità del sistema di gestione dei dati. Sfruttati in modo appropriato, i trigger possono costituire un elemento chiave per il successo delle applicazioni e dei sistemi che si basano su di essi.
