MySQL - Triggers


How can we define a trigger?

    id CHAR(10),
    sub_id CHAR(8) , INDEX(sub_id)

    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(, 1, 8);

    ON SomeTable FOR EACH ROW 
    SET NEW.sub_id = SUBSTRING(, 1, 8);

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
 ON table_name

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.

What are the 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.

How can we refer to a given column?

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.

What are the 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;

What are the privileges required for triggers?

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.

How can we list defined triggers?

show triggers;
SHOW TRIGGERS LIKE '%trigger_name%'\G

select trigger_schema, trigger_name, action_statement
from information_schema.triggers;

select * from information_schema.triggers where 
information_schema.triggers.trigger_schema like '%your_db_name%';

select * from information_schema.triggers where 
information_schema.triggers.trigger_name like '%trigger_name%' and 
information_schema.triggers.trigger_schema like '%data_base_name%'
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License