BEFORE triggers

By using triggers that run before an update or insert, values that are being updated or inserted can be modified before the database is actually modified. These can be used to transform input from the application (user view of the data) to an internal database format where desired.

These BEFORE triggers can also be used to cause other non-database operations to be activated through user-defined functions.

BEFORE DELETE triggers run before a delete operation. They check the values and raise an error, if necessary.

Examples

The following example defines a DELETE TRIGGER with a complex default:

    CREATE TRIGGER trigger1
      BEFORE UPDATE ON table1
      REFERENCING NEW AS N 
      WHEN (N.expected_delivery_date IS NULL) 
      SET N.expected_delivery_date = N.order_date + 5 days;

The following example defines a DELETE TRIGGER with a cross table constraint that is not a referential integrity constraint:

    CREATE TRIGGER trigger2
      BEFORE UPDATE ON table2
      REFERENCING NEW AS N 
      WHEN (n.salary > (SELECT maxsalary FROM salaryguide WHERE rank = n.position)) 
      SIGNAL SQLSTATE '78000' SET MESSAGE_TEXT = 'Salary out of range');