1x1MrAnt

1SET search_path = University_schema;
2
3CREATE OR REPLACE FUNCTION nbEtudiants()
4RETURNS INTEGER AS $$
5DECLARE e_count INTEGER;
6	BEGIN 
7		SELECT COUNT(*) INTO e_count
8		FROM Inscription;
9		RETURN e_count;
10	END;
11$$ LANGUAGE plpgsql;
12
13SELECT nbEtudiants();
14
15CREATE OR REPLACE FUNCTION save_grade() 
16RETURNS TRIGGER AS $audit$
17	BEGIN 
18		CREATE TABLE IF NOT EXISTS Audit (
19			   	sid     	VARCHAR(10) NOT NULL,
20				cno     	VARCHAR(10) NOT NULL,
21				sectno  	VARCHAR(10) NOT NULL,
22				note    	INT         NOT NULL CHECK (note BETWEEN 0 AND 100),
23				save_date	DATE		NOT NULL,
24				PRIMARY KEY (sid, cno, sectno, save_date)
25		);
26		INSERT INTO Audit VALUES(OLD.sid, OLD.cno, OLD.sectno, OLD.note, CURRENT_DATE);
27		RETURN NEW;
28	END;
29$audit$ LANGUAGE plpgsql;
30
31DROP TRIGGER IF EXISTS auditGrade ON Inscription;
32
33CREATE TRIGGER auditGrade BEFORE UPDATE of note ON Inscription
34FOR EACH ROW EXECUTE PROCEDURE save_grade();