Salve a tutti. Prima di tutto vorrei chiedere scusa a sspintux per quanto riguarda al post
https://www.iprogrammatori.it/forum-programmazione/postgresql-f84/chiarimento-sul-trigger-modalita-before-t45942.html. Forse non mi ero spiegato con le parole giuste poichè sono alle prime armi con postgresql. Il punto, per quanto concerne al post suddetto, è che non avevo capito bene la modalità del
RAISE EXCEPTION
ovvero che mi interrompe la transazione in corso. Per questo non capivo bene il trigger in modalità
BEFORE
e perciò magari mi sono travisato e mi sono fatto passare per "pigro" mentre invece desidero comprendere bene i meccanismi, perciò mi sono loggato al forum. Perciò chiedo ancora venia a sspintux.
Fatta questa doverosa chiarificazione mi sono imbattuto nel seguente esercizio. PS cerco di inserire tutti i dettagli affinchè possa dare sufficienti input.
Si consideri la seguente base di dati relativa all’emissione di biglietti (TICKETS) per voli aerei (FLIGHTS), per i quali gli utenti possono essere in possesso di tessere promozionali (CARDS). Ogni tessera acquista un credito (CREDITS) in miglia percorse corrispondenti al biglietto comprato con la tessera stessa. I possessori della tessera possono accedere a diverse fasce di sconti in base al totale delle miglia percorse con i voli dei biglietti comprati, in particolare per un valore di miglia inferiore a 30 mila si è nella fascia 'BASE' con uno sconto del 5%, da 30 a 50 mila è GOLD con uno sconto del 20% e oltre a 50 mila è PREMIUM con sconto 30%. Il prezzo non scontato del biglietto si suppone proporzionale alle miglia percorse dal volo (price = k * miles ove a k possiamo ad esempio assegnarle il valore 5.5) I passaggi di stato scatenano una notifica (NOTIFY) al possessore della tessera. Ecco le tabelle:
CREATE TABLE card
(
cardno SERIAL PRIMARY KEY,
cod_fisc CHAR(11) UNIQUE,
status VARCHAR(15) DEFAULT 'BASE'
);
CREATE TABLE flight
(
flightid SERIAL PRIMARY KEY,
departuretime TIME NOT NULL,
departurecity VARCHAR(20)NOT NULL,
arrivalcity VARCHAR(20) NOT NULL,
miles REAL NOT NULL
);
CREATE TABLE ticket
(
ticketid SERIAL PRIMARY KEY,
flightid INT REFERENCES flight(flightid),
flightdate DATE,
usr CHAR(11),
cardno INT REFERENCES card(cardno)
);
CREATE TABLE credits
(
cardno INT REFERENCES card(cardno),
flightid INT REFERENCES flight(flightid),
PRIMARY KEY(cardno,flightid)
);
CREATE TABLE notify
(
cardno INT REFERENCES card(cardno),
notifyno INT SERIAL,
notifydate DATE,
oldstatus VARCHAR(15),
newstatus VARCHAR(15),
totalmiles REAL,
PRIMARY KEY (cardno,notifyno)
);
Spiegazioni: lo unique in cod_fisc nella tab card l'ho inserito per ovvie ragioni visto che una persona ha una ed una sola card.
Nella tab ticket non ho fatto nessun collegamento fra il numero della carta cardno e il cardno della tab card poichè un biglietto può essere acquistato sia da un possessore di card (e perciò ha diritto di sconti) che da uno che non la possiede (al quale non verranno praticati sconti). Nel primo caso cardno avrà un certo valore intero (impostato dal serial), mentre nel secondo caso valrà NULL.
La tabella credit, come descritto nel codice, riporta i crediti in miglia per ognuno ( e soltanto per essi) dei passeggeri possessori di card. Le miglia totali di un passeggero, per il passaggio di fascia, verranno calcolate facendo la somma (select sum) delle singole miglia del passeggero.In poche parole l'associazione che lega le tabelle ticket e credit è di carinalità 1:1 con totalità 0 dalla parte dei credit visto che ci sono ticket senza credit (ovvero quelli in cui card = NULL) (ps: io utilizzo la notazione "oltre").
Nella tabella notify vengono riportate le notifiche di cambiamento stato. Abbiamo un'associazione 1:n fra le tabelle card e notify. In essa vi è un riferimento esterno nel senso che ogni notifica è caratterizzata da un notifyno relativo ad un determinato cardno. In essa vengono in oltre registrati la data di notifica (quella attuale), il vecchio status ed il nuovo status (risp. prima e dopo la notifica), le miglia totali al momento della notifica.
La mia idea è quella di creare una funzione
supply_ticket
in grado di fornire i ticket (ovvero inserire le apposite righe nella tabella ticket) una volta che vengono dati negli argomenti l'id del volo
fl_id
, la data di partenza
fl_date
ed il codice fiscale del viaggiatore
usr
.
Ecco il codice della funzione:
CREATE FUNCTION supply_ticket(fl_id INT, fl_date DATE, usr CHAR(11))
RETURNS VOID AS $$
DECLARE
k REAL = 5.5;
m REAL;
tid INT;
cd INT;
BEGIN
IF ((SELECT COUNT(*) FROM card
WHERE cod_fisc = usr)>0 ) THEN
SELECT cardno INTO cd FROM card WHERE cod_fisc = usr;
ELSE
cd = NULL;
END IF;
SELECT miles INTO m FROM flight WHERE flightid=fl_id;
SELECT ltid INTO tid FROM last_ticketid;
tid = tid+1;
UPDATE last_ticketid SET ltid = tid;
INSERT INTO ticket VALUES (tid,fl_id,fl_date,usr,cd,m*k);
END;
$$ LANGUAGE plpgsql;
Nelle dichiarazioni k è la costante euro per miglia percorse (che abbiamo settato a 5.5). In primo luogo andiamo a vedere nella tabella card se esiste un viaggiatore con codice fiscale usr (in soldoni viene rilevato automaticamente se il viaggiatore ha una tessera sconto). Se il viaggiatore ha una tessera allora l'id viene inserito in cd, altrimenti viene posto cd = NULL.
Adesso occorre fare un'altra cosa. Visto che l'inserimento nella tab ticket dovrebbe scatenare un trigger che, nel caso in cui cardno non sia null, aggiorna i crediti, aggiorna le eventuali notifiche ed applica lo sconto sul price, se io definsco ticketid serial al momento dell'inserimento dei dati in ticket (riga 19 della funzione supply_ticket) non dovrei passare alcun valore per ticketid. In tal caso mi risulterebbe (non so se mi sbaglio), dal
RAISE NOTICE
che ho messo nella funzione associata al trigger, che nella variabile NEW del trigger non compare l'attributo NEW.ticketid. Sembra che nel NEW vadano a finire soltanto gli attributi che compaiono nell'insert (ovvero NEW non è una riga della tabella ticket). Difatti il
RAISE NOTICE
mi dice che, pur avendo settato con serial il valore di ticket id, esso nel trigger ha valore NULL. Perciò, affinchè venga passato anche il ticketid nel NEW, ho "fatto un mio generatore serial" salavando nella tabella last_ticketid, nell'attributo ltid, l'id dell'ultimo ticket. A tale scopo ho scritto la funzione:
CREATE FUNCTION initialize_last_ticketid()
RETURNS VOID AS $$
BEGIN
CREATE TABLE last_ticketid
(
ltid INT PRIMARY KEY
);
INSERT INTO last_ticketid VALUES (0);
END;
$$ LANGUAGE plpgsql;
In tal modo il RAISE NOTICE nel trigger mi da il valore corretto di ticketid e quindi mi viene passato anche tale attributo nella variabile NEW.
Ecco qui il codice della funzione user_card e del relativo trigger:
CREATE FUNCTION user_card()
RETURNS TRIGGER AS $label$
DECLARE
old_status VARCHAR(15);
new_status VARCHAR(15);
m REAL;
tot REAL;
discount REAL;
BEGIN
IF (NEW.cardno IS NOT NULL) THEN
RAISE NOTICE'cardno = %, ticketid = %, miles = %',
NEW.cardno, NEW.ticketid,(SELECT miles FROM flight WHERE flightid =NEW.flightid);
INSERT INTO credit VALUES(NEW.cardno,NEW.ticketid,
(SELECT miles FROM flight WHERE flightid =NEW.flightid));
SELECT status INTO old_status FROM card WHERE cardno = NEW.cardno;
SELECT SUM(miles) INTO tot FROM credit WHERE cardno = NEW.cardno;
CASE
WHEN tot<30000 THEN
new_status = 'BASE';
discount = 5;
WHEN tot BETWEEN 30000 AND 50000 THEN
new_status = 'GOLD';
discount = 20;
ELSE
new_status = 'PREMIUM';
discount = 30;
END CASE;
IF old_status != new_status THEN
INSERT INTO
notify(cardno,notifydate,oldstatus,newstatus,totalmile)
VALUES(NEW.cardno,CURRENT_DATE,old_status,new_status,tot);
END IF;
NEW.price = NEW.price * (1- discount/100);
END IF;
RETURN NEW;
END;
$label$
LANGUAGE 'plpgsql';
CREATE TRIGGER user_card BEFORE INSERT ON ticket FOR EACH ROW EXECUTE
PROCEDURE user_card ();
Se cardno non è NULL allora prima di tutto mi stampo, col già citato raise NOTICE, i valori che passo in NEW e che andrò ad inserire in credit.Rilevo il vecchio stato, e poi vado a calcolarmi quello nuovo. Se il vecchio è diverso da quello nuovo allora appunto nelle notifiche. In fine correggo price con lo sconto. Se invece cardno = NULL allora non vengono stampati crediti o notifiche ed il prezzo rimane invariato (ovvero m*k).
Tutto sembrerebbe dover funzionare. Quando voglio stampare un ticket, tramite supply_ticket, per un passeggero sprovvisto di card funziona tutto. Invece quando voglio stampare un biglietto per una persona registrata sulla tabella card mi da il seguente errore:
PSQL ha generato un messaggio :
NOTICE: cardno = 1, ticketid = 2, miles = 3500
CONTEXT: SQL statement "INSERT INTO ticket VALUES ( $1 , $2 , $3 , $4 , $5 , $6 * $7 )"
funzione PL/pgSQL "supply_ticket" linea 17 a istruzione SQL
ERROR: insert or update on table "credit" violates foreign key constraint "credit_ticketid_fkey"
DETAIL: Key (ticketid)=(2) is not present in table "ticket".
CONTEXT: SQL statement "INSERT INTO credit VALUES( $1 , $2 , (SELECT miles FROM flight WHERE flightid = $3 ))"
PL/pgSQL function "user_card" line 11 at istruzione SQL
SQL statement "INSERT INTO ticket VALUES ( $1 , $2 , $3 , $4 , $5 , $6 * $7 )"
PL/pgSQL function "supply_ticket" line 17 at istruzione SQL
-end-
Non riesco a capire la violazione di cui parla