mysql - 如何在 BEFORE INSERT 触发器中创建 IF EXIST 子句以防止插入重复记录？

Question

 
-- Create a trigger to prevent a student from registering for a section that the student is already 
-- registered for. 
USE College ;

DROP TRIGGER IF EXISTS  Registration_Before_Insert; 

DELIMITER $$

          
        CREATE TRIGGER Registration_Before_Insert
        BEFORE INSERT ON Registration 
        FOR EACH ROW 
        
        
        
      BEGIN 
        
        DECLARE Studentid INT; 
        
         SELECT COUNT(*) INTO Studentid FROM Registration WHERE StudentID= NEW.StudentID; 
         IF NEW.Studentid= 100 AND NEW.SectionID= 16 THEN 
				 SIGNAL SQLSTATE VALUE '45000'
				  SET MESSAGE_TEXT= 'The student is already registered for this section';
		END IF ; 

      END $$

DELIMITER ;

INSERT INTO Registration(StudentID, SectionID, Grade)
VALUES(100, 16, 4.2); 

INSERT INTO Registration(StudentID, SectionID, Grade)
VALUES(100, 16, 4.2); 

我正在创建一个 BEFORE UPDATE 触发器，以防止学生注册该学生已经注册的部分，但我不确定如何在触发器中使用 IF EXISTS 子句在插入时调用该触发器同一条记录两次。谁能帮我？这是我的代码？

Answer 1

也许你只需要将定义的变量studentid更改为其他内容

DROP TABLE IF EXISTS REGISTRATION;
CREATE TABLE REGISTRATION(StudentID INT, SectionID INT, Grade DECIMAL(10,2));


DROP TRIGGER IF EXISTS  Registration_Before_Insert; 
DELIMITER $$
       CREATE TRIGGER Registration_Before_Insert
        BEFORE INSERT ON Registration 
        FOR EACH ROW 
      BEGIN 
       DECLARE counter INT; 
         SELECT COUNT(*) INTO counter FROM Registration WHERE StudentID= NEW.StudentID
                and new.sectionid = sectionid; 
         IF counter > 0 THEN 
                 SIGNAL SQLSTATE VALUE '45000'
                  SET MESSAGE_TEXT= 'The student is already registered for this section';
        END IF ; 
      END $$

DELIMITER ;

TRUNCATE TABLE REGISTRATION;

INSERT INTO Registration(StudentID, SectionID, Grade)
VALUES(100, 16, 4.2); 

INSERT INTO Registration(StudentID, SectionID, Grade)
VALUES(100, 16, 4.2);