[MySQL] - Trigger? o no?

[Poseidon]

Salve ragazzi, chiedo aiuto perchè sono disperato. Devo ultimare un progetto per un esame universitario e sono a un punto morto. Non sono molto esperto di SQL, anzi diciamo che è solo da un mese che ci sto mettendo mano. Vi spiego che cosa devo realizzare, poi se qualcuno è esperto in materia e riesce ad aiutarmi lo ringrazio fin da subito.

Sto realizzando un database su un sistema di videonoleggio.

Tra le varie tabelle che ho realizzato ce ne sono due in particolare: NOLEGGIO e DVD:

CREATE TABLE noleggio(
id_nol int(11) auto_increment primary key,
DataRestEff date,
Cliente int(11) references cliente(id_cliente),
dvd int(11) references dvd(id_dvd),
inizio timestamp references giorno(data_iniz),
tipo tinyint(1) default 0 COMMENT '0 se è un noleggio passato, 1 se è un
noleggio in corso'
)


CREATE TABLE dvd(
id_dvd int(10) auto_increment primary key,
data_fabb date,
quantita tinyint(1) NOT NULL default '5',
id_film int(4) references film(id_film),
check(quantita >= 0 and quantita <=5)
)


Quello che devo fare a questo punto è questo:

1) Inserire un nuovo noleggio corrente nella tabella NOLEGGIO. Di conseguenza l’attributo quantità nella tabella DVD relativo al dvd che è noleggiato viene decrementato di 1 unità automaticamente . E' possibile inserire un noleggio corrente solo se l’attributo quantità relativo al dvd che si vuole noleggiare è maggiore di zero.

2) Aggiorna un noleggio da corrente a passato nella tabella NOLEGGIO, nel momento in cui il dvd viene restituito. Di conseguenza l’attributo quantità nella tabella DVD relativo al dvd che è noleggiato viene incrementato di una unità automaticamente.

Vi prego se c'è qualcuno che sa metterci mano e ha tempo da perderci mi date una mano? :( sono disperato

[Alhazred]

Un appunto sulla creazione delle tabelle.
Per il campo id_nol di noleggio e id_dvd di dvd puoi evitare di scrivere NOT NULL, i campi auto_increment non saranno mai nulli, inoltre primary key sottointende NOT NULL.
NOT NULL lo metteri per il campo quantita di DVD invece, qui sono andato a occhio perché non so come lo hai pensato.

Che tipo di progetto è? Una web application? Che linguaggio stai utilizzando?

[Poseidon]

Grazie per le dritte ;) adesso sistemo il codice. E' un progetto per un piccolo database su un sistema di videonoleggio. Uso MySQL.

Per quanto riguarda il problema specifico che ho proposto, nessuna idea?

[micmen]

Ma è un progetto java stand-alone usando JDBC ?

[Poseidon]

umm no java non c'entra niente, sto usando MySQL

[Alhazred]

Si, ma la domanda penso sia questa in realtà: il database, una volta messo in funzione, farà tutto da solo e dall'esterno si potrà interagire su di esso solo tramite la console di MySQL o sarà possibile gestirlo con un qualche programma in dotazione ad esempio al proprietario della videoteca?
Non penso si possa fare senza avere un software che interagisce col database, sennò chi gli dice che un dvd è stato affittato oppure che è stato restituito? Ad un cliente che vuole sapere i film in listino oppure allo stesso proprietario della videoteca penso tu debba presentare un interfaccia diversa dalla console di MySQL, non dovrà mica scrivere
select * from film;

[micmen]

Quoto Alhazred. Un sw. è composto di solito da 3 strati: Interfacce grafiche, Manager e Gestione dei Dati. Per intenderci, i controlli di cui parli (ad es. verifica quantità DVD >0) vanno fatti nel programma (Manager) che interagisce col DB (Gestione Dati). Se non è Java, sarà C, C++, VISUAL BASIC, COBOL...

[Poseidon]

aah ok capito il dubbio: no non mi serve alcuna interfaccia grafica, perkè è parte di 1 progetto per un esame universitario, e non mi si richiede alcuna interfaccia grafica.

[micmen]

Quindi deduco che ti servono solo delle query che eseguono le operazioni di cui parlavi.

[Poseidon]

Esattamente :) .. io ho pensato 1 codice di questo tipo, ma non funziona e ovviamente non essendo 1 esperto non ne capisco il motivo:


delimiter //
create trigger decrementa_quantita
after insert on noleggio
for each row
BEGIN
IF dvd.id_dvd in (select dvd.id_dvd
from dvd join noleggio
on dvd.id_dvd = noleggio.dvd
where DataRestEff > now())
THEN
update dvd
set quantita = quantita - 1
END IF;
END; //
create trigger incrementa_quantita
after update of tipo and DataRestEff on noleggio
for each row
BEGIN
IF dvd.id_dvd in (select dvd.id_dvd
from dvd join noleggio
on noleggio.dvd = dvd.id_dvd
where noleggio.DataRestEff < now())
THEN
update dvd
set quantita = quantita + 1
END IF;
END; //
delimiter;

[micmen]

Da cosa capisci che non funziona, hai dei messaggi d'errore o semplicemente vedi che le operazioni non vengono eseguite ? O addirittura non ti crea i trigger ?

[Poseidon]

dunque se eseguo questo codice nel mio database, in cui le due tabelle coinvolte dal trigger sono come quelle definite sopra, usando MySQL Query Browser mi da semplicemente errore.

"You tried to execute an empty string."

Cosa che mi sembra assurda.

[micmen]

La creazione delle tabelle a me è ok, mentre quando creo i trigger mi da errore sul secondo quando trova l'and. Ma come condizione del trigger non basterebbe solo "on update of DataRestEff" che da quanto capisco è la data di restituzione del dvd che viene aggiornata da una funzione ad hoc al momento della restituzione del dvd ?

[Poseidon]

beh la DataRestEff viene aggiornata manualmente, inserendo la data di restituzione del dvd, nel momento quello viene restituito, non ho scritto alcuna particolare funziona per questo.

Tu dici quindi di ridurre il codice a questo? ..

delimiter //
create trigger decrementa_quantita
after insert on noleggio
for each row
BEGIN
IF dvd.id_dvd in (select dvd.id_dvd
from dvd join noleggio
on dvd.id_dvd = noleggio.dvd)
THEN
update dvd
set quantita = quantita - 1
END IF;
END; //
create trigger incrementa_quantita
after update of DataRestEff on noleggio
for each row
BEGIN
IF dvd.id_dvd in (select dvd.id_dvd
from dvd join noleggio
on noleggio.dvd = dvd.id_dvd
where noleggio.DataRestEff < now())
THEN
update dvd
set quantita = quantita + 1
END IF;
END; //
delimiter;

?

[micmen]

Credo che se si aggiorna DataRestEff vuol dire che il dvd è stato restituito, quindi come condizione dovrebbe essere sufficiente.

[Poseidon]

okappa, potresti quindi scrivermi il codice così come lo useresti tu? così lo testo e ti dico se va

[micmen]

Il codice di cosa ?

[Poseidon]

di tutto il trigger, la tua versione insomma, come lo faresti tu

[micmen]

Come ti ho detto toglierei solo la condizione sul tipo nella clausola "on update" del secondo trigger.

[Poseidon]

:D ho risolto! ecco il codice finale e funzionante:

create trigger videoteca.decrementa_quantita
after insert on noleggio
for each row
UPDATE dvd
SET quantita=quantita-1 where id_dvd = new.dvd_noleggiato;


create trigger videoteca.incrementa_quantita
after update on noleggio
for each row
UPDATE dvd
SET quantita=quantita+1 where new.tipo = 1 and id_dvd = new.dvd_noleggiato;



ora però ho 1 altro problema, devo fare in modo che l'inserimento nella tabella noleggio sia lecito solo quando il cliente che ha restituito il precedente noleggio non è in ritardo

create trigger consenti_noleggio
before insert on noleggio
for each row
IF socio=0 and DataRestEff > DataRestPrev or DataRestPrev < now()
THEN abort while DataRestEff <= (DataRestEff, interval 30 day);


sottolineo che non ho la minima idea se l'ultima riga abbia un senso in SQL. Il succo è che se un non socio mi consegna in ritardo, gli blocco la possibilità di noleggiare per 30 giorni.

[micmen]

Scusa, ma vedo degli attributi non presenti nelle tabelle di cui parlavi all'inizio (socio, DataRestPrev). Anche io penso che l'ultima riga non vada bene. Cosa indicherebbe (DataRestEff, interval...) ?

[Poseidon]

ops.. si scusa, allora

- " socio " è un attributo di una tabella chiamata CLIENTE.

- "DataRestPrev è un attributo di una tabella chiamata GIORNO ( che indica il giorno di noleggio, con la data iniziale del noleggio e quella di restituzione prevista )

lo scopo del trigger è quello di impedire un nuovo noleggio ( x 30 giorni a partire dalla restituzione ) a un cliente che non sia socio ( socio = 0 ) che abbia riconsegnato il precedente noleggio in ritardo rispetto alla data di restituzione prevista ( che cade una settimana dopo la data di inizio noleggio )

eh, l'ultima riga indicherebbe appunto il fatto che mi blocca l'inserimento in noleggio per 30 giorni a partire dalla data di riconsegna effettiva se questa è in ritardo. So che è sbagliata, ma non riesco a trovar online alcuno spunto per capire come scriverla correttamente.

edit: a meno che, pensavo.. non si possa introdurre una clausola check nella definizione della tabella noleggio, xò richeiderebb eun join suppongo o più d'uno, è possibile mettere dei join all'interno di una check nella definizione di una tabella?

[micmen]

Domanda: ma se un cliente non è socio come fai ad identificarlo quando ritenta un noleggio ? Cmq. a pelle mi viene in mente una tabella (magari chiamata BlackList) contenente gli attributi Cliente, DataFineBlocco. Quando viene riconsegnato in ritardo un dvd, automaticamente un trigger inserisce una riga in tale tabella. Quindi, quando un cliente tenta il noleggio, prima si controlla se è presente in BlackList. Poi ci potrebbe essere una procedura giornaliera che giornalmente ripulisce la BlackList.

[Poseidon]

allora ogni cliente è identificato da un id_cliente univoco ( chiave primaria ) e c'è 1 attributo "socio" che se impostato a 0 indica che il cliente non è un socio, e se impostato a 1 indica che il cliente è socio.

Comunque cambiando argomento, ma rimanendo in tema è possibile creare un trigger che abbia effetto sulla tabella target dalla quale è invocato?

del tipo

create trigger
after update on TABELLA TARGET
for each row
update TABELLA TARGET ecc ecc.. ?

[Poseidon]

inoltre..

Date due tabelle di partenza:

1) NOLEGGIO

2) DVD, che ha diversi attributi tra qui l'attributo quantita

ha senso il seguente trigger?

create trigger noleggi_consentiti
before insert on noleggio
check dvd.quantita >0 and dvd.quantita<=5;

Il fatto è la quantità di dvd disponibili deve essere compresa tra 0 e 5, ho provato a implementare la tabella dvd inserendovi interno una clausola check, ma MYSQL la ignora.

[micmen]

Nel senso che non ti crea il controllo o che hai provato e non te lo considera ?

[Poseidon]

nel senso che se implemento una tabella così:

create table noleggio (

ecc ecc

quantita tinyint(1) default 5 not null,

ecc ecc

check(quantita >=0 and quantita<=5)
);

MySQL non mi rileva alcune errore, xò la check non viene proprio considerata. Ho controllato online e MySQL proprio non digerisce le check nelle tabelle. Non trovo 1 modo per implementar il controllo :( avevo pensato mediante trigger, ma non riesco..