首先,我认为这是一个数据规则,因此应该集中执行。也就是说,应该有一个由 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()
(or COLUMNS_UPDATED
如果您认为可以依赖列排序)和/或deleted
and inserted
概念表来限制查询的范围以及何时触发。您还需要确保事务正确序列化以防止并发问题。虽然涉及,但并不是非常复杂。
我强烈推荐这本书,第 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)
);
然后添加一些“帮助”存储过程/函数来维护更新顺序。