Esercizi trigger

Esercizi relativi ai triggers (lezione di riferimento: Regole attive per basi di dati)

Esercizio 1

Siano date le seguente relazione:

• Attivita(IDAttivita, SuperAttivita, Livello, Nome, DataEsecuzione, Risultato)

SuperAttivita rappresenta l'attività padre contenente la sotto attività indicata (modello ad albero) e livello rappresenta il livello dell'albero in cui il nodo si trova (partendo la 0 per la radice). Si definiscano i seguenti triggers:

1. Quando un'attività è eliminata, anche tutte le sue sotto'attività vengono eliminate;
2. SuperAttivita deve assumere solo NULL se è radice, oppure l'id attività di un altro record. Impedire la creazione e modifica per valori diversi di SuperAttivita;
3. Ad ogni INSERT calcolare il livello corrispondente.

Soluzione

1. Possibile SQL:

CREATE TRIGGER OnDeletionActivity
AFTER DELETE ON Attivita
FOR EACH ROW
DELETE FROM Attivita
WHERE SuperAttivita = old.IDAttivita;

Si ricordi che la ricorsione è generata da ogni DELETE: il trigger viene chiamato, trova dei figli e elimina i figli. Per ogni figlio eliminato il trigger è chiamato ancora, e così via...

2. Viene mostrata il trigger per INSERT (per UPDATE il trigger è pressoché identico):

CREATE TRIGGER CheckSuperIns
AFTER INSERT ON Attivita
FOR EACH ROW
WHEN NEW.SuperAttivita != NULL AND 
     NEW.SuperAttivita NOT IN (SELECT SuperAttivita FROM Attivita)

ROLLBACK;

3. Soluzione per insert:

CREATE TRIGGER SetLevel
AFTER INSERT ON Attivita
FOR EACH ROW
BEGIN
    UPDATE Attivita SET Livello = 0 WHERE IDAttivita = NEW.IDAttivita AND SuperAttivita = NULL;

    UPDATE Attivita SET
        Livello = 1 + (SELECT Livello FROM Attivita WHERE IDAttivita = NEW.SuperAttivita) 
        WHERE IDAttivita = NEW.IDAttivita AND SuperAttivita != NULL
END;

Nota: si ricorda che scrivere

 SET NEW.Livello = ...

è semanticamente errato: utilizzando AFTER INSERT, il record è già stato scritto, la modifica non sarebbe riflessa sulla base di dati ma solo sull'oggetto volatile NEW.

Esercizio 2

Sia data la seguente base di dati, che gestisce un semplice sistema di Mailing List:

• MailingList(IDML, Nome, Indirizzo)
• MailingListMessage(IDMessaggio, IDML, Testo)
• Categoria(IDCategoria,Keyword, Nome)
• Subject(IDML, Keyword)
• User(IDUser, Nome, Cognome, EMail)
• Interest(IDUser, Keyword)
• ToBeSend(IDTBS, IDMessaggio, EMailAddress, EmailBody)

La tabella ToBeSend, conterrà i messaggi che dovranno essere inviati agli utenti, e sarà processata da un programma esterno, che eliminerà man mano i messaggi inviati. IDMessaggio può essere NULL se non rieferito a uno in particolare (es: messaggi di avviso, ecc.).

Si vogliono definire i seguenti trigger:

1. Quando un messaggio è inserito in una mailing list, tutti gli utenti abbonati ad almeno una keyword corrispondente dovranno ricevere il messaggio;
2. Se un messaggio è eliminato, tutti i messaggi in coda che attendono di essere inviati devono essere eliminati;
3. Se una mailing list viene eliminata, tutti i messaggi e tutti i messaggi in coda relativi dovranno essere cancellati e un messaggio di avviso inviato a tutti gli utenti abbonati a quelle keyword;
4. avvertire gli utenti in caso di cambio del nome o dell'email di una mailing list.

Soluzione

1.

CREATE TRIGGER AlertOnInsert
AFTER INSERT ON MailingListMessage
FOR EACH ROW
INSERT INTO ToBeSend(IDMessaggio, EMailAddress, EmailBody)  
    SELECT NEW.IDMessaggio, EMail,NEW.Testo FROM User INNER JOIN Interest 
     WHERE Interest.Keyword = ANY (SELECT s.Keyword FROM Subject s WHERE s.IDML = NEW.IDML)
    ;

2.

CREATE TRIGGER DeletionSending
AFTER DELETE ON MailingListMessage
FOR EACH ROW
DELETE FROM ToBeSend
WHERE IDMessaggio=OLD.IDMessaggio;

3.

CREATE TRIGGER DeletionMailingList
AFTER DELETE ON MailingList
FOR EACH ROW
BEGIN
    DELETE FROM ToBeSend
    WHERE IDMessaggio IN (SELECT IDMessaggio FROM MailingListMessage WHERE IDML=OLD.IDML);

    DELETE FROM MailingListMessage
    WHERE IDML=OLD.IDML;

    INSERT INTO ToBeSend(IDMessaggio, EMailAddress, EmailBody)
       (SELECT NULL, EMail, "Mailing list eliminata!" FROM User
       INNER JOIN Interest 
       WHERE Interest.Keyword IN (SELECT Keyword FROM Subject WHERE IDML = OLD.IDML)
       )
    ; 
END;

4.

CREATE TRIGGER UpdateML
AFTER UPDATE ON MailingList
FOR EACH ROW
INSERT INTO ToBeSend(IDMessaggio, EMailAddress, EmailBody)
    (SELECT NULL, EMail, "Mailing list modificata!" FROM User
       INNER JOIN Interest 
       WHERE Interest.Keyword IN (SELECT Keyword FROM Subject WHERE IDML = OLD.IDML)
       )