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 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50
| CREATE TABLE Client
(CodCL int IDENTITY(1, 1)
,NomCL varchar(30)
,PrenomCL varchar(30)
,Compte float
,CONSTRAINT PK_Client PRIMARY KEY (CodCL)
)
CREATE TABLE Produit
(CodPro int IDENTITY(1, 1)
,NomPro varchar(30)
,PrixPro float
,CONSTRAINT PK_Produit PRIMARY KEY (CodPro)
)
CREATE TABLE Achat
(CodCL int NOT NULL
,CodPro int NOT NULL
,qt int NOT NULL
,CONSTRAINT PK_Achat PRIMARY KEY (CodCL, CodPro)
,CONSTRAINT FK_Achat_CodCL FOREIGN KEY (CodCL) REFERENCES Client(CodCL)
,CONSTRAINT FK_Achat_CodPro FOREIGN KEY (CodPro) REFERENCES Produit(CodPro)
)
GO
CREATE TRIGGER Verif_Solde_Client ON Achat
INSTEAD OF INSERT
AS BEGIN
DECLARE @MontantTransaction float
DECLARE @SoldeClient float
SET @MontantTransaction = (SELECT qt * PrixPro FROM INSERTED INNER JOIN Produit ON Inserted.CodPro = Produit.CodPro)
SET @SoldeClient = (SELECT Compte FROM INSERTED INNER JOIN Client ON Inserted.CodCL = Client.CodCL)
PRINT @MontantTransaction
PRINT @SoldeClient
IF @MontantTransaction <= @SoldeClient
INSERT INTO Achat SELECT * FROM INSERTED
END
GO
INSERT INTO Client (NomCL, PrenomCL, Compte) VALUES('Jean', 'Brugnat', 123.45)
INSERT INTO Client (NomCL, PrenomCL, Compte) VALUES('Jeanne', 'Serva', 456.78)
INSERT INTO Client (NomCL, PrenomCL, Compte) VALUES('Gilles', 'Hollu', 159.26)
INSERT INTO Produit (NomPro, PrixPro) VALUES('Pomme', 1)
INSERT INTO Produit (NomPro, PrixPro) VALUES('Banane', 2)
INSERT INTO Produit (NomPro, PrixPro) VALUES('Ananas', 3)
INSERT INTO Achat VALUES(2, 3, 100)
INSERT INTO Achat VALUES(1, 2, 50)
INSERT INTO Achat VALUES(3, 1, 200)
GO
SELECT * FROM Achat |
Partager