触发器，断言和检查之间有什么区别(在数据库中) [英] what is the difference between triggers, assertions and checks (in database)

问题描述

谁能解释(或推荐一个网站或论文)触发器、断言和检查之间的确切区别，并描述我应该在哪里使用它们?

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

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

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

推荐答案

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

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:

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.

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

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:

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

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

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

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

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.

让事情变得更加混乱 - 触发器可用于强制执行检查约束，并且在某些 DB 中可以代替断言(通过允许您运行与正在修改的表无关的代码).初学者的一个常见错误是在需要触发器时使用检查约束或在需要检查约束时使用触发器.

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.

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

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.

这篇关于触发器，断言和检查之间有什么区别(在数据库中)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！