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

Question

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

EDIT: I mean in database, not in any other system or language.

Answer 1

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.

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)

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

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.

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.

Answer 2

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.

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.

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.