Bonjours à tous !

J'ai un problème et sa fait trop longtemps que je cherche la solution alors poste ici en espérant trouver de l'aide.

Je cherche à créer une contrainte entre deux tables (Emprunts et Stocks) dans ma base de données Postgresql.
Dans la table Stocks il y a une colonne qauntité.
Dans la table Emprunts, une ligne correspond à l'emprunt d'un objet du stock. Si le champ DateRetour est NULL c'est que l'objet est en ce moment emprunté, sinon c'est qu'il a été rendu à la date indiquée.
Le champ quantité reste fixe. Le nombre d'exemplaire d'objet disponible (non emprunté en ce moment) est calculé avec la différence de la quantité avec le nombre de lignes de cette objet dans la table Emprunts.
Donc la contrainte que je veux exprimer est la suivante : la quantité d'un objet dans la table Stocks doit être supérieur ou égal aux nombre de lignes concernant ce même objet dans la table Emprunts.

Voici la structure des deux tables :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE Stocks (
	IdFilm		INTEGER	REFERENCES Films(IdFilm),
	IdFormat	INTEGER	REFERENCES Formats(IdFormat),
	Quantite	INTEGER	CHECK (Quantite > 0),
	PRIMARY KEY (IdFilm, IdFormat)
);
 
CREATE TABLE Emprunts (
	IdEmp		INTEGER	PRIMARY KEY,
	IdCompte	INTEGER	NOT NULL REFERENCES Comptes(IdCompte),
	IdFilm		INTEGER	NOT NULL,
	IdFormat	INTEGER	NOT NULL,
	DateEmp		date	NOT NULL DEFAULT current_date,
	DateRetour	date	CHECK (DateEmp <= DateRetour),
	DureeMax	INTEGER	NOT NULL DEFAULT 5 CHECK (dureemax > 0),
	FOREIGN KEY (IdFilm, IdFormat) REFERENCES Stocks(IdFilm, IdFormat)
);
Il faut savoir que les assertions ne sont pas implémenté et que le langage plpgsql n'est pas présent.

J'ai essayé de la manière suivante :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- On ne peut pas faire de sous-requête dans un CHECK donc on va utiliser deux fonctions.
 
CREATE FUNCTION nb_emprunts(INTEGER, INTEGER) RETURNS BIGINT
AS '
SELECT NbEmp
FROM ( SELECT Emprunts.IdFilm, Emprunts.IdFormat, Count(*) AS NbEmp
       FROM Emprunts
       WHERE DateRetour IS NULL AND Emprunts.IdFilm = $1 AND Emprunts.IdFormat = $2
       GROUP BY Emprunts.IdFilm, Emprunts.IdFormat ) AS R;
' LANGUAGE SQL;
 
CREATE FUNCTION quantite(INTEGER, INTEGER) RETURNS INTEGER
AS '
SELECT Quantite
FROM Stocks
WHERE IdFilm = $1 AND IdFormat = $2;
' LANGUAGE SQL;
 
ALTER TABLE Stocks ADD CONSTRAINT quantite_superieur_aux_emprunts
CHECK ( Quantite >= nb_emprunts(IdFilm, IdFormat) );
 
ALTER TABLE Emprunts ADD CONSTRAINT quantite_superieur_aux_emprunts
CHECK ( quantite(IdFilm, IdFormat) >= nb_emprunts(IdFilm, IdFormat) );
 
-- Problème : la vérification se fait avant l'insertion
--            donc on peut insérer un élément violant la contrainte.
Mais cette solution ne marche pas. On peut ajouter un emprunt de trop. Je pense que c'est parce que les fonctions exécutent leurs requêtes avant l'insertion de la nouvelle ligne.

Je me suis demander si je pouvais faire ça avec un trigger qui, après une insertion, vérifie la contrainte et lève une exception pour ne pas valider l'insertion. Mais je ne vois pas comment faire.

Avez-vous une solution à me proposer ?
Si oui, je vous en remercie d'avance !