[successivo] [precedente] [inizio] [fine] [indice generale] [indice ridotto] [translators] [docinfo] [indice analitico] [volume] [parte]

Capitolo 515. Grilletti per il controllo della validità esterna

Nel momento in cui si inseriscono, modificano o eliminano dei valori per una certa relazione, può essere importante fare in modo di rifiutare le azioni che non sono valide, in base al contenuto di altre relazioni. Di solito, questo tipo di controllo può essere dichiarato in fase di creazione della relazione; tuttavia, un DBMS limitato potrebbe ignorare tali dichiarazioni.

In questo capitolo si mostra l'uso dei grilletti per imporre dei vincoli di validità dipendenti dal contenuto di altre relazioni.

515.1 Controllo del codice articolo tra la relazione «Movimenti» e la relazione «Articoli»

Nel capitolo precedente sono stati creati due grilletti, denominato Movimenti_ins e Movimenti_upd, con lo scopo di impedire l'inserimento (o la modifica) di valori impossibili per la quantità e per il valore del movimento. Questi due grilletti vengono ripresi ed estesi, allo scopo di impedire che possano essere inseriti movimenti riferiti ad articoli inesistenti, in quanto non ancora dichiarati nella relazione Articoli; inoltre ne viene aggiunto un altro, per impedire che un articolo possa essere eliminato dalla relazione Articoli, se questo risulta essere ancora utilizzato nella relazione Movimenti.

Pertanto, si crei il file grilletti-movimenti-articoli.sql, contenente il testo seguente, sostituendo le metavariabili con informazioni appropriate e rispettando la punteggiatura:

-- Creazione dei grilletti "Movimenti_ins", "Movimenti_upd" e "Articoli_del"
-- Esercizio di: cognome nome classe
-- Data: data
-- File: grilletti-movimenti-articoli.sql

CREATE TRIGGER Movimenti_ins
       BEFORE INSERT ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            END;
       END;

CREATE TRIGGER Movimenti_upd
       BEFORE UPDATE ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            END;
       END;

CREATE TRIGGER Articoli_del
       BEFORE DELETE ON Articoli
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN ((SELECT Articolo FROM Movimenti WHERE Articolo = OLD.Articolo) IS NOT NULL)
            THEN
                RAISE (ABORT, 'L''articolo non può essere rimosso, perché è utilizzato nella relazione Movimenti!')
            END;
       END;

Una volta completato e salvato il file grilletti-movimenti-articoli, se ne deve controllare il funzionamento con la base di dati, ma prima vanno rimossi i grilletti Movimenti_ins e Movimenti_upd, che qui vengono ricreati. Basta eseguire i passaggi seguenti:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> DROP TRIGGER Articoli_ins;[Invio]

sqlite> DROP TRIGGER Articoli_upd;[Invio]

sqlite> .quit[Invio]

Quando i grilletti preesistenti sono stati rimossi, si può eseguire il file grilletti-movimenti-articoli.sql nella base di dati:

$ sqlite3 mag.db < grilletti-movimenti-articoli.sql[Invio]

Se non si ottiene alcun messaggio da parte del programma, la creazione dei grilletti dovrebbe essere avvenuta con successo, altrimenti, è stato commesso un errore. Per rimediare all'errore, si devono prima cancellare i grilletti (questa volta sono tre: Movimenti_ins, Movimenti_upd e Articoli_del), quindi si può ritentare l'inserimento del comando (ammesso che il file grilletti-movimenti-articoli.sql sia stato corretto di conseguenza).

Per verificare che i vincoli dichiarati funzionino come previsto, si può provare a inserire un movimento che fa riferimento a un articolo inesistente; quindi, si può provare a cancellare un articolo che risulta invece movimentato:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> INSERT INTO Movimenti[Invio]

...> VALUES (11, 777, 2, '2006-01-25', 1, NULL, 1000, 500.00);[Invio]

INSERT INTO Movimenti VALUES (11, 777, 2, '2006-01-25', 1, NULL, 1000, 500.00);
SQL error: Il codice articolo non è presente nella relazione Articoli!

sqlite> UPDATE Movimenti SET Articolo = 777 WHERE Movimento = 2;[Invio]

UPDATE Movimenti SET Articolo = 777 WHERE Movimento = 2;
SQL error: Il codice articolo non è presente nella relazione Articoli!

sqlite> DELETE FROM Articoli WHERE Articolo = 2;[Invio]

DELETE FROM Articoli WHERE Articolo = 2;
SQL error: L'articolo non può essere rimosso, perché è utilizzato nella relazione Movimenti!

sqlite> .quit[Invio]

515.2 Controllo del codice cliente tra la relazione «Movimenti» e la relazione «Clienti»

Vengono qui ripresi i grilletti Movimenti_ins e Movimenti_upd, aggiungendo il grilletto Clienti_del, con lo scopo di impedire che possano essere inseriti movimenti riferiti a clienti inesistenti (in quanto non ancora dichiarati nella relazione Clienti) e di impedire la cancellazione di un cliente quando questo risulta essere ancora utilizzato nella relazione Movimenti.

Pertanto, si crei il file grilletti-movimenti-clienti.sql, contenente il testo seguente, sostituendo le metavariabili con informazioni appropriate e rispettando la punteggiatura:

-- Creazione dei grilletti "Movimenti_ins", "Movimenti_upd" e "Clienti_del"
-- Esercizio di: cognome nome classe
-- Data: data
-- File: grilletti-movimenti-clienti.sql

CREATE TRIGGER Movimenti_ins
       BEFORE INSERT ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            END;
       END;

CREATE TRIGGER Movimenti_upd
       BEFORE UPDATE ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            END;
       END;

CREATE TRIGGER Clienti_del
       BEFORE DELETE ON Clienti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN ((SELECT Cliente FROM Movimenti WHERE Cliente = OLD.Cliente) IS NOT NULL)
            THEN
                RAISE (ABORT, 'Il cliente non può essere rimosso, perché è utilizzato nella relazione Movimenti!')
            END;
       END;

A differenza dell'esempio che appare nella sezione precedente, l'attributo Cliente della relazione Movimenti può contenere il valore nullo (NULL). Per questa ragione, il grilletto verifica prima che il valore inserito non sia nullo, poi che il codice cliente esista nella relazione Clienti.

Una volta completato e salvato il file grilletti-movimenti-clienti, se ne deve controllare il funzionamento con la base di dati, ma prima vanno rimossi i grilletti Movimenti_ins e Movimenti_upd, che qui vengono ricreati. Basta eseguire i passaggi seguenti:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> DROP TRIGGER Articoli_ins;[Invio]

sqlite> DROP TRIGGER Articoli_upd;[Invio]

sqlite> .quit[Invio]

Quando i grilletti preesistenti, associati alla relazione Movimenti, sono stati rimossi, si può eseguire il file grilletti-movimenti-clienti.sql nella base di dati:

$ sqlite3 mag.db < grilletti-movimenti-clienti.sql[Invio]

Se non si ottiene alcun messaggio da parte del programma, la creazione dei grilletti dovrebbe essere avvenuta con successo, altrimenti, è stato commesso un errore. Per rimediare all'errore, si devono prima cancellare i grilletti (questa volta sono tre: Movimenti_ins, Movimenti_upd e Clienti_del), quindi si può ritentare l'inserimento del comando (ammesso che il file grilletti-movimenti-clienti.sql sia stato corretto di conseguenza).

Per verificare che i vincoli dichiarati funzionino come previsto, si può provare a inserire un movimento che fa riferimento a un cliente inesistente; quindi, si può provare a cancellare un articolo che risulta invece movimentato:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> INSERT INTO Movimenti VALUES[Invio]

...> (11, 101, 2, '2006-01-25', 999, NULL, 1000, 500.00);[Invio]

INSERT INTO Movimenti VALUES (11, 101, 2, '2006-01-25', 999, NULL, 1000, 500.00);
SQL error: Il codice cliente non è presente nella relazione Clienti!

sqlite> UPDATE Movimenti SET Cliente = 999 WHERE Movimento = 2;[Invio]

UPDATE Movimenti SET Cliente = 999 WHERE Movimento = 2;
SQL error: Il codice cliente non è presente nella relazione Clienti!

sqlite> DELETE FROM Clienti WHERE Cliente = 2;[Invio]

DELETE FROM Clienti WHERE Cliente = 2;
SQL error: Il cliente non può essere rimosso, perché è utilizzato nella relazione Movimenti!

sqlite> .quit[Invio]

515.3 Verifica sulla creazione dei grilletti «Movimenti_ins», «Movimenti_upd» e «Causali_del»

Si prepari il file grilletti-movimenti-causali.sql, modificando il file grilletti-movimenti-clienti.sql, in modo da riutilizzare quanto già scritto nei grilletti Movimenti_ins e Movimenti_upd. Si segua lo scheletro seguente, tenendo conto che si vuole impedire l'inserimento nella relazione Movimenti di causali inesistenti e che si vuole impedire la cancellazione di una causale, dalla relazione Causali, se questa risulta utilizzata nella relazione Movimenti (in pratica, per questa funzione ulteriore, si deve aggiungere il grilletto Causali_del).

Figura 515.13. Scheletro del file grilletto-movimenti-causali.sql, da completare.

-- Creazione dei grilletti "Movimenti_ins", "Movimenti_upd" e "Causali_del"
-- Esercizio di: cognome nome classe
-- Data: data
-- File: grilletti-movimenti-causali.sql

CREATE TRIGGER Movimenti_ins
       BEFORE INSERT ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            WHEN ...
            THEN
                ...
            END;
       END;

CREATE TRIGGER Movimenti_upd
       BEFORE UPDATE ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            WHEN ...
            THEN
                ...
            END;
       END;

CREATE TRIGGER Causali_del
       BEFORE DELETE ON Causali
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN ...
            THEN
                ...
            END;
       END;

Una volta completato e salvato il file grilletti-movimenti-causali, se ne deve controllare il funzionamento con la base di dati, ma prima vanno rimossi i grilletti Movimenti_ins e Movimenti_upd, che qui vengono ricreati. Basta eseguire i passaggi seguenti:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> DROP TRIGGER Articoli_ins;[Invio]

sqlite> DROP TRIGGER Articoli_upd;[Invio]

sqlite> .quit[Invio]

Quando i grilletti preesistenti, associati alla relazione Movimenti, sono stati rimossi, si può eseguire il file grilletti-movimenti-causali.sql nella base di dati:

$ sqlite3 mag.db < grilletti-movimenti-causali.sql[Invio]

Se non si ottiene alcun messaggio da parte del programma, la creazione dei grilletti dovrebbe essere avvenuta con successo, altrimenti, è stato commesso un errore. Per rimediare all'errore, si devono prima cancellare i grilletti (tutti), quindi si può ritentare l'inserimento del comando (ammesso che il file grilletti-movimenti-causali.sql sia stato corretto di conseguenza).

Si consegni per la valutazione la stampa del file grilletti-movimenti-causali.sql.

515.4 Verifica sulla creazione dei grilletti «Movimenti_ins», «Movimenti_upd» e «Fornitori_del»

Si prepari il file grilletti-movimenti-fornitori.sql, modificando il file grilletti-movimenti-causali.sql, in modo da riutilizzare quanto già scritto nei grilletti Movimenti_ins e Movimenti_upd. Si segua lo scheletro seguente, tenendo conto che si vuole impedire l'inserimento nella relazione Movimenti di fornitori inesistenti e che si vuole impedire la cancellazione di un fornitore, dalla relazione Fornitori, se questo risulta utilizzato nella relazione Movimenti (in pratica, per questa funzione ulteriore, si deve aggiungere il grilletto Fornitori_del).

Si osservi che nella relazione Movimenti, l'attributo Fornitore può avere un valore nullo.

Figura 515.15. Scheletro del file grilletto-movimenti-fornitori.sql, da completare.

-- Creazione dei grilletti "Movimenti_ins", "Movimenti_upd" e "Fornitori_del"
-- Esercizio di: cognome nome classe
-- Data: data
-- File: grilletti-movimenti-fornitori.sql

CREATE TRIGGER Movimenti_ins
       BEFORE INSERT ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            WHEN ...
            THEN
                ...
            WHEN ...
                  AND ...
            THEN
                ...
            END;
       END;

CREATE TRIGGER Movimenti_upd
       BEFORE UPDATE ON Movimenti
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN (NEW.Quantita <= 0)
            THEN
                RAISE (ABORT, 'La quantità non può essere inferiore o uguale a zero!')
            WHEN (NEW.Valore < 0)
            THEN
                RAISE (ABORT, 'Il valore caricato non può essere inferiore a zero!')
            WHEN ((SELECT Articolo FROM Articoli WHERE Articolo = NEW.Articolo) IS NULL)
            THEN
                RAISE (ABORT, 'Il codice articolo non è presente nella relazione Articoli!')
            WHEN ((NEW.Cliente IS NOT NULL)
                  AND ((SELECT Cliente FROM Clienti WHERE Cliente = NEW.Cliente) IS NULL))
            THEN
                RAISE (ABORT, 'Il codice cliente non è presente nella relazione Clienti!')
            WHEN ...
            THEN
                ...
            WHEN ...
                  AND ...
            THEN
                ...
            END;
       END;

CREATE TRIGGER Fornitori_del
       BEFORE DELETE ON Fornitori
       FOR EACH ROW
       BEGIN
            SELECT CASE
            WHEN ...
            THEN
                ...
            END;
       END;

Una volta completato e salvato il file grilletti-movimenti-fornitori, se ne deve controllare il funzionamento con la base di dati, ma prima vanno rimossi i grilletti Movimenti_ins e Movimenti_upd, che qui vengono ricreati. Basta eseguire i passaggi seguenti:

$ sqlite3 mag.db[Invio]

SQLite version ...
Enter ".help" for instructions

sqlite> DROP TRIGGER Articoli_ins;[Invio]

sqlite> DROP TRIGGER Articoli_upd;[Invio]

sqlite> .quit[Invio]

Quando i grilletti preesistenti, associati alla relazione Movimenti, sono stati rimossi, si può eseguire il file grilletti-movimenti-fornitori.sql nella base di dati:

$ sqlite3 mag.db < grilletti-movimenti-fornitori.sql[Invio]

Se non si ottiene alcun messaggio da parte del programma, la creazione dei grilletti dovrebbe essere avvenuta con successo, altrimenti, è stato commesso un errore. Per rimediare all'errore, si devono prima cancellare i grilletti (tutti), quindi si può ritentare l'inserimento del comando (ammesso che il file grilletti-movimenti-fornitori.sql sia stato corretto di conseguenza).

Si consegni per la valutazione la stampa del file grilletti-movimenti-fornitori.sql.

515.5 Conclusione

Prima di passare al capitolo successivo, si deve riprendere il file magazzino.sql e vi si devono sostituire le istruzioni per la creazione dei grilletti Movimenti_ins e Movimenti_upd, come contenuto nel file grilletti-movimenti-fornitori.sql; inoltre vanno aggiunti i grilletti Articoli_del, Causali_del, Clienti_del e Fornitori_del, come sono stati realizzati in questo capitolo.

Si osservi che la dichiarazione dei grilletti va collocata dopo la creazione della relazione a cui fanno riferimento e prima delle istruzioni che inseriscono delle tuple nella stessa relazione.

Una volta aggiornato il file magazzino.sql come descritto, si deve cancellare il file mag.db e ricreare a partire dalle istruzioni contenute nel file magazzino.sql:

$ sqlite3 mag.db < magazzino.sql[Invio]

Se vengono segnalati degli errori, occorre correggere il file magazzino.sql, cancellare nuovamente il file mag.db, quindi si deve ripetere l'operazione. La base di dati contenuta nel file mag.db, viene usata nel capitolo successivo e non si può proseguire se non si riesce a ricrearla correttamente.

Dovrebbe essere possibile fare riferimento a questa pagina anche con il nome grilletti_per_il_controllo_della_validita_esterna.htm

[successivo] [precedente] [inizio] [fine] [indice generale] [indice ridotto] [translators] [docinfo] [indice analitico]