MySQL - Triggers

A trigger is a database object that is associated with a table and that is defined to activate (or "trigger") when a particular kind of event occurs for that table. The events for which triggers can be defined are INSERT, DELETE, and UPDATE. A trigger provides a mean to execute a SQL statement when you insert, update, or delete rows in a table.

A given trigger is defined for only one of these events, but you can define multiple triggers for a table, one trigger per type of event.

Triggers can be defined to activate either before or after the event. This means, there can be two trigger per event (one activate before the event, one activate after the event).

CREATE TRIGGER trigger_name
 {BEFORE | AFTER}
 {INSERT | UPDATE | DELETE}
 ON table_name
 FOR EACH ROW
  trigger_statement

The FOR EACH ROW means that execution of trigger_statement happens once for each row being inserted, updated, or deleted; not for each row currently in the table. This means, a trigger activates multiple times for a statement that affect several rows.

trigger_statement must be a single statement, but if necessary, you can use a compound statement.

A table cannot have two triggers for the same combination of activation time and event.

Restrictions on triggers:

  1. You cannot use the CALL statement.
  2. You cannot begin or end transaction.
  3. You cannot create a trigger for a TEMPORARY table or view
  4. Trigger creation is subjected to the same binary log-related restriction placed on stored routine.

To refer to a given column, prefix the column name with a qualifier OLD to refer to a value from the original row, or NEW to refer to a value in the new row.

In an INSERT trigger, NEW.col_name indicates a column value to be inserted into a new row. OLD is not allowed.

In a DELETE trigger, NEW is not allowed.

In an UPDATE trigger, OLD.col_name and NEW.col_name refer to the value of the column in the row before and after the row is updated, respectively.

OLD must be used in a read-only fashion. NEW can be used to read or change column values.

Benefits of using triggers:

  1. A trigger can examine row values to be inserted or updated, and it can determine what values were deleted, or what they were updated to.
  2. A trigger can change values before they are inserted into a table, or used to update a table. This capability enables the use of trigger as data filter.
  3. You can modify how INSERT, DELETE, or UPDATE work. For example, during an INSERT, you can provide a default value that is based on the current time for columns with any temporal data type. Normally, only TIMESTAMP columns can be initialized to the current time automatically.
DROP TRIGGER world.Capital_bi;

To create or destroy triggers with CREATE TRIGGER or DROP TRIGGER, you must have SUPER privilege. If the trigger use OLD or NEW, you must have additional privileges: To assign the value of a column with SET NEW.col_name = value, you must have UPDATE privilege for the column. To use NEW.col_name in an expression, you must have SELECT privilege for the column.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License