SQL 触发器，断言和检查之间有什么区别（在数据库中）

what is the difference between triggers, assertions and checks (in database)

提问by Am1rr3zA

Can anybody explain (or suggest a site or paper) the exact difference between triggers, assertions and checks, also describe where I should use them?

任何人都可以解释（或推荐一个网站或论文）触发器、断言和检查之间的确切区别，还可以描述我应该在哪里使用它们吗？

EDIT: I mean in database, not in any other systems or programing languages.

编辑：我的意思是在数据库中，而不是在任何其他系统或编程语言中。

回答by jellomonkey

Triggers- a trigger is a piece of SQL to execute either before or after an update, insert, or delete in a database. An example of a trigger in plain English might be something like: before updating a customer record, save a copy of the current record. Which would look something like:

触发器- 触发器是在更新、插入或删除数据库之前或之后执行的一段 SQL。简单英语的触发器示例可能类似于：在更新客户记录之前，保存当前记录的副本。这看起来像：

CREATE TRIGGER triggerName
AFTER UPDATE
    INSERT INTO CustomerLog (blah, blah, blah)
    SELECT blah, blah, blah FROM deleted

---

---

The difference between assertions and checks is a little more murky, many databases don't even support assertions.

断言和检查之间的区别有点模糊，许多数据库甚至不支持断言。

Check Constraint- A check is a piece of SQL which makes sure a condition is satisfied before action can be taken on a record. In plain English this would be something like: All customers must have an account balance of at least $100 in their account. Which would look something like:

检查约束- 检查是一条 SQL，它确保在对记录采取操作之前满足条件。用简单的英语来说，这将类似于：所有客户的帐户中必须有至少 100 美元的帐户余额。这看起来像：

ALTER TABLE accounts 
ADD CONSTRAINT CK_minimumBalance
CHECK (balance >= 100)

Any attempt to insert a value in the balance column of less than 100 would throw an error.

任何在余额列中插入小于 100 的值的尝试都会引发错误。

Assertions- An assertion is a piece of SQL which makes sure a condition is satisfied or it stops action being taken on a database object. It could mean locking out the whole table or even the whole database.

断言——断言是一段 SQL，它确保满足条件或停止对数据库对象采取的操作。这可能意味着锁定整个表甚至整个数据库。

To make matters more confusing - a trigger could be used to enforce a check constraint and in some DBs can take the place of an assertion (by allowing you to run code un-related to the table being modified). A common mistake for beginners is to use a check constraint when a trigger is required or a trigger when a check constraint is required.

更令人困惑的是 - 可以使用触发器来强制执行检查约束，并且在某些数据库中可以代替断言（通过允许您运行与正在修改的表无关的代码）。初学者的一个常见错误是在需要触发器时使用检查约束或在需要检查约束时使用触发器。

An example: All new customers opening an account must have a balance of $100; however, once the account is opened their balance can fall below that amount. In this case you have to use a trigger because you only want the condition evaluated when a new record is inserted.

一个例子：所有开户的新客户必须有 100 美元的余额；但是，一旦开设账户，其余额可能会低于该金额。在这种情况下，您必须使用触发器，因为您只希望在插入新记录时评估条件。

回答by Erwin Smout

In the SQL standard, both ASSERTIONS and CHECK CONSTRAINTS are what relational theory calls "constraints" : rules that the data actually contained in the database must comply with.

在 SQL 标准中，ASSERTIONS 和 CHECK CONSTRAINTS 都是关系理论所说的“约束”：数据库中实际包含的数据必须遵守的规则。

The difference between the two is that CHECK CONSTRAINTS are, in a sense, much "simpler" : they are rules that relate to one single row only, while ASSERTIONs can involve any number of other tables, or any number of other rows in the same table. That obviously makes it (much !) more complex for the DBMS builders to support it, and that is, in turn, the reason why they don't : they just don't know how to do it.

两者之间的区别在于 CHECK CONSTRAINTS 在某种意义上更“简单”：它们是仅与单行相关的规则，而 ASSERTION 可以涉及任意数量的其他表，或同一行中的任意数量的其他行桌子。这显然使 DBMS 构建者支持它（非常！）变得更加复杂，这反过来也是他们不支持的原因：他们只是不知道如何去做。

TRIGGERs are pieces of executable code of which it can be declared to the DBMS that those should be executed every time a certain kind of update operation (insert/delete/update) gets done on a certain table. Because triggers can raise exceptions, they are a MEANS for implementing the same thing as an ASSERTION. However, with triggers, it's still the programmer who has to do all the coding, and not make any mistake.

TRIGGER 是一段可执行代码，它可以向 DBMS 声明这些代码应该在每次在某个表上完成某种更新操作（插入/删除/更新）时执行。因为触发器可以引发异常，所以它们是实现与断言相同的东西的方法。但是，对于触发器，仍然是程序员必须完成所有编码，并且不能犯任何错误。

EDIT

编辑

Onedaywhen's comments re. ASSERTION/CHECK cnstr. are correct. The difference is way more subtle (AND confusing). The standard indeed allows subqueries in CHECK constraints. (Most products don't support it though, so my "relate to a single row" is true for most SQL products, but not for the standard.) So is there still a difference ? Yes there still is. More than one even.

Onedaywhen 的评论重新。断言/检查 cnstr。是正确的。差异更加微妙（并且令人困惑）。该标准确实允许 CHECK 约束中的子查询。（尽管大多数产品不支持它，所以我的“与单行相关”适用于大多数 SQL 产品，但不适用于标准。）那么还有区别吗？是的，还有。甚至不止一个。

First case : TABLE MEN (ID:INTEGER) and TABLE WOMEN(ID:INTEGER). Now imagine a rule to the effect that "no ID value can appear both in the MEN and in the WOMEN table". That's a single rule. The intent of ASSERTION is precisely that the database designer would state this single rule [and be done with it], and the DBMS would know how to deal with this [efficiently, of course] and how to enforce this rule, no matter what particular update gets done to the database. In the example, the DBMS would know that it has to do a check for this rule upon INSERT INTO MEN, and upon INSERT INTO WOMEN, but not upon DELETE FROM MEN/WOMEN, or INSERT INTO <anyothertable>.

第一种情况：男桌 (ID:INTEGER) 和女桌 (ID:INTEGER)。现在想象一个规则，“没有 ID 值可以同时出现在 MEN 和 WOMEN 表中”。这是一个单一的规则。ASSERTION 的意图恰恰是数据库设计者将声明这个单一规则 [并完成它]，并且 DBMS 将知道如何处理这个 [当然，有效地] 以及如何强制执行该规则，无论什么特殊对数据库进行更新。在该示例中，DBMS 会知道它必须在 INSERT INTO MEN 和 INSERT INTO WOMEN 时检查此规则，但不检查 DELETE FROM MEN/WOMEN 或 INSERT INTO <anyothertable>。

But DBMS's aren't smart enough for doing all that. So what needs to be done ? The database designer must add TWO CHECK constraints to his database, one to the MEN table (checking newly inserted MEN ID's against the WOMEN table) and one to the WOMAN table (checking the other way round). There's your first difference : one rule, one ASSERTION, TWOCHECK constraints. CHECK constraints are a lower level of abstraction than ASSERTIONs, because they require the designer to do more thinking himself about (a) all the kinds of update that could potentially cause his ASSERTION to be violated, and (b) what particular check should be carried out for any of the specific "kinds of update" he found in (a). (Although I don't really like making "absolute" statements on what is still "WHAT" and what is "HOW", I'd summarize that CHECK constraints require more "HOW" thinking (procedural) by the database designer, whereas ASSERTIONs allow the database designer to focus exclusively on the "WHAT" (declarative).)

但是 DBMS 还不够智能，无法完成所有这些工作。那么需要做什么呢？数据库设计者必须向他的数据库添加两个 CHECK 约束，一个添加到 MEN 表（根据 WOMEN 表检查新插入的 MEN ID），另一个添加到 WOMAN 表（反过来检查）。这是你的第一个区别：一个规则，一个断言，两个检查约束。CHECK 约束是比 ASSERTION 更低的抽象级别，因为它们要求设计者自己更多地思考 (a) 可能导致其 ASSERTION 被违反的所有类型的更新，以及 (b) 应该进行哪些特定检查他在（a）中发现的任何特定的“更新类型”。（虽然我真的不喜欢对什么是“什么”和什么是“如何”做出“绝对”的陈述，但我总结说 CHECK 约束需要数据库设计者更多的“如何”思考（程序性），而断言允许数据库设计人员专注于“什么”（声明性）。）

Second case (though I'm not entirely sure of this - so take with a grain of salt) : just your average RI rule. Of course you are used to specify this using some REFERENCES clause. But imagine that a REFERENCES clause was not available. A rule like "Every ORDER must be placed by a known CUSTOMER" is really just that, a rule, thus : a single ASSERTION. However, we all know that such a rule can always be violated in two ways : inserting an ORDER (in this example), and deleting a CUSTOMER. Now, in line with the foregoing MAN/WOMEN example, if we wanted to implement this single rule/ASSERTION using CHECK constraints, then we'd have to write a CHECK constraint that checks CUSTOMER existence upon insertions into ORDER, but what CHECK constraint could we write that does whatever is needed upon deletionfrom CUSTOMER ? They simply aren't designed for that purpose, as far as I can tell. There's your second difference : CHECK constraints are tied to INSERTs exclusively, ASSERTIONS can define rules that will also be checked upon DELETEs.

第二种情况（虽然我不完全确定 - 所以请持保留意见）：只是您的平均 RI 规则。当然，您习惯于使用某些 REFERENCES 子句来指定这一点。但想象一下，没有可用的 REFERENCES 子句。像“每个订单都必须由已知客户下单”这样的规则实际上就是一个规则，因此：单个断言。然而，我们都知道这样的规则总是可以通过两种方式违反：插入一个 ORDER（在这个例子中），和删除一个 CUSTOMER。现在，根据前面的 MAN/WOMEN 示例，如果我们想使用 CHECK 约束来实现这个单一规则/ASSERTION，那么我们必须编写一个 CHECK 约束来在插入到 ORDER 时检查 CUSTOMER 是否存在，但是什么 CHECK 约束可以我们写道，在删除时做任何需要的事情来自客户？据我所知，它们根本不是为此目的而设计的。还有第二个区别：CHECK 约束仅与 INSERT 相关联，ASSERTIONS 可以定义也将在 DELETE 时检查的规则。

Third case : Imagine a table COMPOS (componentID:... percentage:INTEGER), and a rule to the effect that "the sum of all percentages must at all times be equal to 100". That's a single rule, and an ASSERTION is capable of specifying that. But try and imagine how you would go about enforcing such a rule with CHECK constraints ... If you have a valid table with, say, three nonzero rows adding up to a hundred, how would you apply any change to this table that could survive your CHECK constraint ? You can't delete or update(decrease) any row without having to add other replacing rows, or update the remaining rows, that sum up to the same percentage. Likewise for insert or update (increase). You'd need deferred constraint checking at the very least, and then what are you going to CHECK ? There's your third difference : CHECK constraints are targeted to individual rows, while ASSERTIONs can also define/express rules that "span" several rows (i.e. rules about aggregations of rows).

第三种情况：想象一个表 COMPOS (componentID:... percent:INTEGER)，以及一个规则，“所有百分比的总和必须始终等于 100”。这是一个单一的规则，并且 ASSERTION 能够指定它。但是试着想象一下你将如何使用 CHECK 约束来强制执行这样的规则......如果你有一个有效的表，比如三个非零行加起来等于 100，你将如何对这个表应用任何可以生存的更改你的 CHECK 约束？您不能删除或更新（减少）任何行而不必添加其他替换行或更新剩余行，总和为相同的百分比。同样用于插入或更新（增加）。您至少需要延迟约束检查，然后您要检查什么？这是你的第三个区别：

回答by Mohamed Adel

Assertions do not modify the data, they only check certain conditions

断言不修改数据，它们只检查某些条件

Triggers are more powerful because the can check conditions and also modify the data

触发器更强大，因为它可以检查条件并修改数据

--------------------------------------------------------------------------------

-------------------------------------------------- ------------------------------

Assertions are not linked to specific tables in the database and not linked to specific events

断言不链接到数据库中的特定表，也不链接到特定事件

Triggers are linked to specific tables and specific events

触发器链接到特定的表和特定的事件

回答by onedaywhen

A database constraint involves a condition that must be satisfied when the database is updated. In SQL, if a constraint condition evaluates to false then the update fails, the data remains unchanged and the DBMS generates an error.

数据库约束涉及更新数据库时必须满足的条件。在 SQL 中，如果约束条件评估为 false，则更新失败，数据保持不变，DBMS 生成错误。

Both CHECKand ASSERTIONare database constraints defined by the SQL standards. An important distinction is that a CHECKis applied to a specific base table, whereas an ASSERTIONis applied to the whole database. Consider a constraint that limits the combined rows in tables T1and T2to a total of 10 rows e.g.

双方CHECK并ASSERTION通过SQL标准定义数据库约束。一个重要的区别是 aCHECK应用于特定的基表，而 anASSERTION应用于整个数据库。考虑到在表中限制了组合行的约束T1和T2至总共10行例如

CHECK (10 >= (
              SELECT COUNT(*)
                FROM (
                      SELECT *
                        FROM T1
                      UNION
                      SELECT * 
                        FROM T2
                     ) AS Tn
             ))

Assume the tables are empty. If this was applied as an ASSERTIONonly and a user tried to insert 11 rows into T1then then the update would fail. The same would apply if the constraint was applied as a CHECKconstraint to T1only. However, if the constraint was applied as a CHECKconstraint to T2only the constraint would succeed because a statement targeting T1does not cause the constraints applied to T1to be tested.

假设表是空的。如果这ASSERTION仅作为唯一应用并且用户尝试将 11 行插入其中，T1则更新将失败。如果CHECK约束T1仅作为约束应用，则同样适用。但是，如果将约束作为CHECK约束应用于T2仅约束将成功，因为语句定位T1不会导致应用的约束T1被测试。

Both an ASSERTIONand a CHECKmay be deferred (if declared as DEFERRABLE), allowing for data to temporarily violate the constraint condition, but only within a transaction.

anASSERTION和 aCHECK都可以延迟（如果声明为DEFERRABLE），允许数据暂时违反约束条件，但仅限于事务内。

ASSERTIONand CHECKconstraints involving subqueries are features outside of core Standard SQL and none of the major SQL products support these features. MS Access (not exactly an industrial-strength product) supports CHECKconstraints involving subqueries but not deferrable constraints plus constraint testing is always performed on a row-by-row basis, the practical consequences being that the functionality is very limited.

ASSERTION和CHECK约束涉及子查询是核心标准 SQL 之外的功能，并且没有一个主要的 SQL 产品支持这些功能。MS Access（不完全是工业级产品）支持CHECK涉及子查询的约束，但不支持延迟约束，而且约束测试总是逐行执行，实际结果是功能非常有限。

In common with CHECKconstraints, a trigger is applied to a specific table. Therefore, a trigger can be used to implement the same logic as a CHECKconstraint but not an ASSERTION. A trigger is procedural code and, unlike constraints, the user must take far more responsibility for concerns such as performance and error handling. Most commercial SQL products support triggers (the aforementioned MS Access does not).

与CHECK约束一样，触发器应用于特定表。因此，触发器可用于实现与CHECK约束相同的逻辑，但不能用于实现ASSERTION. 触发器是过程代码，与约束不同，用户必须对诸如性能和错误处理等问题承担更多的责任。大多数商业 SQL 产品都支持触发器（前面提到的 MS Access 不支持）。

回答by Feri

The expression should be true for trigger to fire, but check will be evaluated wherever the expression is false.

触发器触发时表达式应该为真，但只要表达式为假，就会评估检查。