Hello I have a question: I am doing a trigger for some tables.
The trigger has to move the students with 3 grades(not F) to another table called diploma.
The thing is the trigger isn't working and I don't know how to fix this. please enlighten me.
What I have tried:
CREATE TABLE GRADE
(
STUDENTNUMBER INT NOT NULL,
SECTIONID INT NOT NULL,
GRADE CHAR (1),
PRIMARY KEY (SECTIONID, STUDENTNUMBER),
FOREIGN KEY (STUDENTNUMBER) REFERENCES STUDENT
);
CREATE TABLE STUDENT
(
STUDENTNAME VARCHAR (30) NOT NULL,
STUDENTNUMBER INT NOT NULL,
CLASS VARCHAR (30),
SECTIONID INT[] NOT NULL,
MAJOR VARCHAR (20),
PRIMARY KEY (STUDENTNUMBER)
);
CREATE FUNCTION test() RETURNS TRIGGER AS $$
DECLARE
x INTEGER;
BEGIN
SELECT COUNT(GRADE) INTO x FROM GRADE, student WHERE GRADE.STUDENTNUMBER = student.STUDENTNUMBER;
IF (X >= 3) THEN
INSERT INTO diploma (STUDENTNAME,STUDENTNUMBER , CLASS,SECTIONID,MAJOR) values
(SELECT student.STUDENTNAME, student.STUDENTNUMBER, student.CLASS, student.SECTIONID, student.MAJOR);
END IF;
RETURN x;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER UPDATE_TEST
AFTER INSERT OR UPDATE ON GRADE
FOR EACH STATEMENT EXECUTE PROCEDURE
test();