我如何...制作一个触发器，将元素从表格移动到另一个表格 [英] How do i...make a trigger that moves elements from a table to another one

问题描述

你好我有一个问题：我正在为某些牌桌做触发。



触发器必须将3个等级（不是F）的学生移动到另一个表叫文凭。



事情是触发器不起作用，我不知道如何解决这个问题。请赐教。



我的尝试：

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();

推荐答案

DECLARE
x INTEGER;
BEGIN
SELECT COUNT（GRADE） INTO x FROM GRADE，学生 WHERE GRADE.STUDENTNUMBER = student.STUDENTNUMBER;
IF （X> = 3 ）那么
INSERT INTO 文凭（STUDENTNAME，STUDENTNUMBER，CLASS，SECTIONID，MAJOR ）值
（ SELECT student.STUDENTNAME，student.STUDENTNUMBER，student.CLASS，student。 SECTIONID，student.MAJOR）;
END IF ;
返回 x;
END ;

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 或 更新 ON GRADE
FOR 每个陈述 EXECUTE PROCEDURE
test（）;

LANGUAGE plpgsql; CREATE TRIGGER UPDATE_TEST AFTER INSERT OR UPDATE ON GRADE FOR EACH STATEMENT EXECUTE PROCEDURE test();

这篇关于我如何...制作一个触发器，将元素从表格移动到另一个表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！