The context is like this:
I have a table named "Pruefung" where the Attribute "Theorie" is a boolean(this tells you, if a drivers license test is theory (when 1) oder practical(when 0)).
Then there is a table called "Schueler" with an Attribute called "Email"
Then a Table named "Fahrstunde" with an attribute called "dauer" which tells you, how long a practical lesson took (in minutes)
Now before an insert on Pruefung can happen, i have to make sure that the "schueler"(which is german for student) has at least 180 minutes of pratical driving lessons.
Here are the tables:
CREATE TABLE IF NOT EXISTS Pruefung
(
PruefungsID int not null primary key check(PruefungsID > 0) ,
Theorie boolean not null,
Gebuehr float not null check(Gebuehr > 0 and round(Gebuehr, 2) = Gebuehr),
Bestanden boolean not null,
Email varchar[50] not null collate nocase ,
foreign key(Email) references Schueler(Email) on update cascade on delete cascade
);
CREATE TABLE IF NOT EXISTS Schueler
(
Email varchar[50] primary key not null collate nocase
Geschlecht varchar[50] not null collate nocase check(length(Geschlecht)>0 and Geschlecht in('m', 'w', 'd')),
AdressID int not null check(AdressID > 0),
foreign key(Email) references Nutzer(Email) on update cascade on delete cascade,
foreign key(AdressID) references Adresse(AdressID) on update cascade on delete cascade
);
CREATE TABLE IF NOT EXISTS Fahrstunde
(
FahrstundeID int primary key not null check(FahrstundeID > 0),
Typ varchar[50] not null collate nocase check(length(Typ)>0 and Typ not glob '*[^ -~]*'),
Dauer int not null check(Dauer > 0 and Dauer % 45 = 0),
Preis float not null check(Preis > 0 and round(Preis, 2) = Preis),
Schueleremail varchar[50] not null collate nocase ,
Fahrlehreremail varchar[50] not null collate nocase ,
Fahrschulemail varchar[50] not null collate nocase ,
foreign key(Schueleremail) references Schueler(Email) on update cascade on delete cascade,
foreign key(Fahrlehreremail) references Fahrlehrer(Email) on update cascade on delete cascade,
foreign key(Fahrschulemail) references Fahrschule(Email) on update cascade on delete cascade
);
when i try to insert in Pruefung a student with the emailadress " schueler6@web.de", it should not work because he only has 90 minutes. but it does get inserted...even though i wrote this trigger.
and i just can find the error and hope for help :D