SQL 使用触发器进行约束

Question

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

我有一个关于使用触发器的问题(老实说，我不太确定如何使用触发器..)

假设我们有一个 Teachers 表。

并且这个老师表包含了 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...)

一个学生想要添加鲍勃的类(class)。但是，我想触发拒绝添加学生。(因为鲍勃的课已经满了..)

但是，我不太确定创建触发器的方法.. :( .. (这是第一次研究触发器......)

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

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概念表来限制查询的范围以及何时触发。您还需要确保事务正确序列化以防止并发问题。虽然涉及，但并不复杂。

我强烈推荐这本书 Applied Mathematics for Database Professionals  By Lex de Haan, Toon Koppelaars ，第 11 章(代码示例是 Oracle，但可以轻松移植到 SQL Server)。

在没有触发器的情况下也可以实现相同的目标。这个想法是在 (teacher_id, students_tally) 上创建一个 super key 将在注册中引用，为此将维护一个独特的学生事件序列，并通过测试该序列永远不会超过最大计数。

这是一些基本的 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)
);

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