Difference Between Assertion ,Trigger,Check Constraints

1) ASSERTION 

DEFINITION

IT IS A STATEMENT IN SQL WHICH CAN ENSURE CERTAIN CONDITION IS ALWAYS EXIST IN DATABASE .

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.

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

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

=========================================================

2) 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.

===========================================================

3) 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

---

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

Triggers are linked to specific tables and specific events.

=============================================================