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.