BEgBxW58

1SET search_path = University_schema;
2
3-- 1)
4CREATE OR REPLACE FUNCTION nbEtudiants()
5RETURNS INT AS $$
6	BEGIN
7		RETURN	(SELECT COUNT(*)
8				FROM Etudiant);
9	END
10$$ LANGUAGE plpgsql;
11
12-- 2)
13CREATE OR REPLACE FUNCTION add_to_audit() RETURNS TRIGGER AS $add_to_audit$
14	BEGIN
15		CREATE TABLE IF NOT EXISTS Audit(
16				dateModif	Date	NOT NULL,
17				sid		VARCHAR(10) NOT NULL,
18				cno		VARCHAR(10) NOT NULL,
19				sectno 	VARCHAR(10)	NOT NULL,
20				note	INT			NOT NULL CHECK (note BETWEEN 0 AND 100),
21				PRIMARY KEY (sid, cno, sectno),
22				FOREIGN KEY (sid) REFERENCES Etudiant(sid),
23				FOREIGN KEY (cno, sectno) REFERENCES Section(cno, sectno)
24		);
25		INSERT INTO Audit VALUES (current_timestamp, OLD.sid, OLD.cno, OLD.sectNo, OLD.note);
26	END;
27$add_to_audit$ LANGUAGE plpgsql;
28
29CREATE TRIGGER auditGrade 
30BEFORE UPDATE OF note 
31ON Inscription
32EXECUTE PROCEDURE add_to_audit();