SQL使用触发器进行约束

Question

我正在研究触发器和约束。

我有一个使用触发器的问题（说实话，我不确定如何使用触发器。）

假设我们有一个教师表。

这个教师表包含Teacher_id，ssn，first_name，last_name，class_time

例如，

|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

和

假设学生人数上限为25（学生人数上限由老师定义，因此可以是10、22、25 …等任意数字）

一个学生想添加鲍勃的班级。但是，我想触发拒绝添加学生的触发器。（因为鲍勃的班级已满。）

但是，我不确定如何创建触发器.. :( ..（这是第一次研究触发器…。）

任何人都可以帮助创建示例代码来理解触发器部分吗？

Answer 1

首先，我认为这是一条数据规则，因此应集​​中实施。也就是说，应该由DBMS强制执行数据库约束（或等效约束），以防止所有应用程序写入不良数据（而不是依靠每个应用程序的各个编码器来避免写入不良数据）。

其次，我认为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''s student tally is too high to accept new students.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

但是，还有一些其他注意事项。在每次UPDATE访问表之后执行此类查询可能效率很低。您应该使用UPDATE()（或COLUMNS_UPDATED如果您认为可以依赖列排序）和/或deleted和inserted概念表来限制查询的范围以及何时触发查询。您还需要确保事务正确序列化，以防止并发问题。尽管涉及到，但并不复杂。

我强烈推荐《面向数据库专业人员的应用数学》一书，作者是Lex
de Haan，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)
);

然后添加一些“帮助”存储的过程/函数以保持更新顺序。