CONCEPTES BÀSICS

A trigger is an object associated with a table that is activated when an action occurs against that table.

Usage example

A trigger is set to fire when we insert a new grade outside the range (0-10). When attempting to insert the grade 11, it will NOT allow us to do so.

Each DBMS implements its own triggers; in the case of MySQL we have some restrictions:

  • The trigger name is UNIQUE system-wide; we cannot use the same name against different tables.
  • The trigger CANNOT call a stored procedure (using the CALL statement).
  • Two triggers cannot exist that execute against the same table at the same time (but they can at different times).

With triggers, the execution timing is very important. Triggers execute BEFORE making the action or AFTER making the action.

TRIGGER EXAMPLE

The trigger is responsible for accumulating sales made throughout the day. It is associated with the table comptes (num, quantitat).

mysql> CREATE TRIGGER disp1 BEFORE INSERT ON comptes FOR EACH ROW SET @sum=@sum+NEW.quantitat;

INSERT INTO comptes VALUES (1,100),(2,50);

The name of the trigger will be disp1, it will activate before performing the action, and what it will do is add each sale to a global variable called sum.

STATEMENTS

We have seen in the previous example that the table columns associated with the trigger can be referenced using the aliases OLD and NEW. OLD.nom_columna refers to a column in an existing row, before being updated or deleted. NEW.nom_columna refers to a column in a new row about to be inserted, or in an existing row after being updated.

Deleting a trigger

To delete a trigger, the SUPER privilege is required

DROP TRIGGER nombre_disp

Error handling

During trigger execution, MySQL handles errors as follows:

  • If a BEFORE trigger fails, the operation is not executed.
  • An AFTER trigger executes only if the BEFORE trigger (if any) and the operation execute successfully.
  • An error during the execution of a trigger results in an error for the statement that caused it.
  • In transactional tables, a trigger error should cause a rollback of all changes made. In non-transactional tables, any change made before the error should not be affected. (A transactional table example would be the process of withdrawing money from bank ATMs)