SQL using a trigger to limit

Question

I study triggers and limitations.

And I had a question about using a trigger (to be honest, I'm not sure how to use a trigger ..)

Say we have a table of Masters.

And this teacher table contains teacher_id, ssn, first_name, last_name, class_time

eg,

|teacher_id|ssn    | first_name | last_name | student_number| max_student
|1         |1234   | bob        | Smith     | 25            |25
|2         |1235   | kim        | Johnson   | 24            |21
|3         |1236   | kally      | Jones     | 23            |22

and

let's say that the maximum number of students will be 25. (the maximum number of students will be determined by the teacher, so this can be any number, for example 10, 22, 25 ...)

and the student wants to add a bob class. However, I want to make a trigger that refuses to add a student (because the bob class is already full.)

However, I'm not quite sure how to create a trigger .. :( .. (this is the first time you need to learn a trigger ....)

Can anyone help create a code sample to understand the trigger part?

Answer 1

-, , , . ( ), , ( , , ).

-, , AFTER ( INSTEAD OF).

-, CHECK.

, , , , .

, . , student_number ; , , - student_id:

WITH EnrolmentTallies
     AS
     (
      SELECT teacher_id, COUNT(*) AS students_tally
        FROM Enrolment
       GROUP 
          BY teacher_id      
     ) 
SELECT * 
  FROM Teachers AS T
       INNER JOIN EnrolmentTallies AS E
         ON T.teacher_id = E.teacher_id
            AND E.students_tally > T.students_tally;

SQL Server :

CREATE TRIGGER student_tally_too_high ON Enrolment
AFTER INSERT, UPDATE
AS
IF EXISTS (
           SELECT * 
             FROM Teachers AS T
                  INNER JOIN (
                              SELECT teacher_id, COUNT(*) AS students_tally
                                FROM Enrolment
                               GROUP 
                                  BY teacher_id      
                             ) AS E
                                  ON T.teacher_id = E.teacher_id
                                     AND E.students_tally > T.students_tally
          )
BEGIN
RAISERROR ('A teachers' student tally is too high to accept new students.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

. UPDATE . UPDATE() ( COLUMNS_UPDATED, ) / deleted inserted, . , concurrency. , .

, Toon Koppelaars, 11 ( - Oracle, SQL Server).

---

, . , (teacher_id, students_tally) , , .

SQL DDL:

CREATE TABLE Students 
(
 student_id INTEGER NOT NULL,
 UNIQUE (student_id)
);

CREATE TABLE Teachers 
(
 teacher_id INTEGER NOT NULL,
 students_tally INTEGER NOT NULL CHECK (students_tally > 0), 
 UNIQUE (teacher_id), 
 UNIQUE (teacher_id, students_tally)
);

CREATE TABLE Enrolment
(
 teacher_id INTEGER NOT NULL UNIQUE,
 students_tally INTEGER NOT NULL CHECK (students_tally > 0), 
 FOREIGN KEY (teacher_id, students_tally)
    REFERENCES Teachers (teacher_id, students_tally)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 student_id INTEGER NOT NULL UNIQUE 
    REFERENCES Students (student_id),
 student_teacher_sequence INTEGER NOT NULL
    CHECK (student_teacher_sequence BETWEEN 1 AND students_tally)
 UNIQUE (teacher_id, student_id), 
 UNIQUE (teacher_id, student_id, student_teacher_sequence)
);

"help" / .