Page 1262 - IT2

11.
CREATE PROC InsertPasser(@CodeControle INT, @CodeElève INT,
@CorrecteurINT,@Note FLOAT)
AS
IF @CodeControle NOT IN(SELECT Code FROM Controle) OR @CodeElève NOT
IN (SELECT Code FROM ELEVE)
OR @Correcteur NOT IN(SELECT Code FROM ENSEIGNANT) OR @CodeElève IN
(SELECT #CodeElève FROM PASSER
WHERE #CodeControle=@CodeControle)
RETURN -1
INSERT INTO PASSER
VALUES(@CodeControle,@CodeElève,@Correcteur,@Note)
RETURN 0

12.
CREATE TABLE HistoriqueGroupe(Num INT IDENTITY PRIMARY KEY,CodeEleve
INT FOREIGN KEY REFERENCES ELEVE(Code),DateChangement DATETIME,
AncienGroupe VARCHAR(10),NouveauGroupe VARCHAR(10))
CREATE TRIGGER Changement
ON ELEVE
FOR UPDATE
AS
DECLARE Cur CURSOR
FOR SELECT I.Code,D.Groupe,I.Groupe FROM INSERTED I INNER JOIN
DELETED D ON I.Code=D.Code
DECLARE @Code INT,
@AncienGroupeVARCHAR(10),
@NouveauGroupeVARCHAR(10)
OPEN Cur
FETCH FROM Cur INTO @Code,@AncienGroupe,@NouveauGroupe
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO
HistoriqueGroupeVALUES(@Code,GETDATE(),@AncienGroupe,@NouveauGroupe)
FETCH FROM Cur INTO @Code,@AncienGroupe,@NouveauGroupe
END
CLOSE Cur
DEALLOCATE Cur

13.
CREATE TRIGGER ModifierCasse
ON ELEVE
FOR INSERT,UPDATE
AS
DECLARE @Code INT
SELECT @Code=Code FROM INSERTED
UPDATE ELEVE
www.itlearning-settat.com

Email: admission@itlearning-settat.com

Tél.:0661077812