Database: Constraint check vs Trigger check

Usage examples

• consistency check (e.g. if the employee address in HRM_apps table is changed the address in the Mailing_apps table should also be changed). It's important to maintain consistency if you have redundancy due to denormalization (in sake of read performance.)
• to avoid business error (e.g. compare the execution price with the historical average price if the stock execution price makes sense)
• recompute the summary table which is used to increase online performance
• security check

Why business check in the database instead of application

In most cases I believe that the business logic should be in the application business layer instead of in the database layer, but there are reasons why people often implement business logics in the database instead of the application:

• reusable and consistency across different applications if the schema is used by multiple applications
• hidden from application (external) programmers (in sake of secrecy and complexity) e.g. stock_evaluation_formula
• performance reason (computing the formula in the database will reduce the network traffic between application and databases, the formula then just send the result to the application, e.g. the datamining/statistics summary computation)

Tips

• It's important that the business logic in the database (e.g. stored procedure, user functions, constraints, triggers) are documented for application developers.
• Use constraint check & triggers sparingly since both of them can lead you to maintenance nightmare.
• Avoid to build your own check if you can use the standard feature e.g. primary key and unique constraints for entity integrity, foreign key constraint for referential integrity.
• Try to simplify the check formula for performance & maintanability.

Constraint check vs Trigger check

• use constraint check to constraint the domain i.e. about your data
• trigger is about how to handle data (e.g. cascading changes, rollback if the business constraint is violated e.g. the merchandised is out of order)

Constraint check example (Oracle):

ALTER TABLE employees ADD CONSTRAINT constraint_salarycheck CHECK (salary<1000>

Test: INSERT INTO employees (employee_id,naam,salary) VALUES (hrseq.nextval,'mr boss',10000);--not permitted

Trigger check example (Oracle):

CREATE OR REPLACE TRIGGER tr_salaryupdatecheck BEFORE UPDATE OF salary ON employees FOR EACH ROW

 BEGIN

  IF :new.salary < :old.salary THEN

   RAISE_APPLICATION_ERROR(-20003,'salary lower');

  END IF;

 END;

Test: UPDATE employees SET salary=100 WHERE name = 'mr boss';

Disable/enable:

ALTER TRIGGER tr_salaryupdatecheck DISABLE;

ALTER TRIGGER tr_salaryupdatecheck ENABLE;

Source: Steve's blogs http://soa-java.blogspot.com/

Any comments are welcome :)

---

References:
Oracle Database 11g & MySQL 5.6 Developer Handbook