CodingBison

MySQL provides triggers to do application-specific tasks based on certain events. The tasks could include keeping track of certain values, providing integrity of input data, and compliance of input data, invoking logging services etc.

MySQL supports triggers for only three query events: UPDATE, DELETE, and INSERT. Further, for these events, a trigger can be added before these events occur (keyword "BEFORE") or after these events occur (keyword "AFTER"). These triggers allow us to add application logic based on these events.

Let us understand the concept of triggers using a handful of examples.

To create a trigger, we can use a "CREATE TRIGGER" statement; the input to this statement includes the name of the table, the event type, and before/after qualifier. Let us create a simple trigger (provided below) that is based on INSERT event for inventory table and uses BEFORE event qualifier. In simpler words, this trigger gets activated just before any insert on that table.

The action taken by the below-provided trigger is simple. It updates a user variable (in MySQL, we use "@" to create a user variable), @total_added with the item_quantity of each row. This trigger gets activated whenever there is an INSERT event (to be precise, right before the INSERT event). Also, we can use NEW keyword to identify the new row; using this keyword, NEW.item_quantity means the value of item_quantity in the row being inserted.

 mysql> SET @total_added = 0;
 Query OK, 0 rows affected (0.00 sec)

 mysql> SELECT * FROM inventory;
 Empty set (0.04 sec)

 mysql> CREATE TRIGGER add_quantity BEFORE INSERT ON inventory 
     -> FOR EACH ROW SET @total_added = @total_added + NEW.item_quantity;
 Query OK, 0 rows affected (0.09 sec)

 mysql> INSERT INTO inventory VALUES(1, 1003, 11);
 Query OK, 1 row affected (0.04 sec)

 mysql> INSERT INTO inventory VALUES(1, 1007, 7); 
 Query OK, 1 row affected (0.00 sec)

 mysql> INSERT INTO inventory VALUES(2, 1003, 11);
 Query OK, 1 row affected (0.00 sec)

 mysql> SELECT * FROM inventory;
 +----------+-----------+---------------+
 | store_id | item_code | item_quantity |
 +----------+-----------+---------------+
 |        2 |      1003 |            11 |
 |        1 |      1007 |             7 |
 |        1 |      1003 |            11 |
 +----------+-----------+---------------+
 3 rows in set (0.00 sec)

 mysql> SELECT @total_added;
 +--------------+
 | @total_added |
 +--------------+
 |           29 |
 +--------------+
 1 row in set (0.00 sec)

The above output reflects that the variable @total_added gets updated with each insert and the final value is a cumulative sum of the item_quantity from each row (11 + 7 + 11 = 29).

We can see existing triggers using SHOW query. And, if needed, we can use DROP query to drop triggers.

 mysql> SHOW TRIGGERS;
 +------------+------+---------+---------------------------------------------------+------+-------+
 | Trigger    | Event| Table   | Statement                                         |Timing|Created|
 +------------+------+---------+---------------------------------------------------+------+-------+
 |add_quantity|INSERT|inventory|SET @total_added = @total_added + NEW.item_quantity|BEFORE|NULL   |
 +------------+------+---------+---------------------------------------------------+------+-------+
 ...
 ...
 ...
 ----------+----------------+----------------------+----------------------+--------------------+
  sql_mode | Definer        | character_set_client | collation_connection | Database Collation |
 ----------+----------------+----------------------+----------------------+--------------------+
           | root@localhost | latin1               | latin1_swedish_ci    | latin1_swedish_ci  |
 ----------+----------------+----------------------+----------------------+--------------------+

 1 row in set (0.01 sec)

 mysql> 
 mysql> DROP TRIGGER add_quantity;
 Query OK, 0 rows affected (0.01 sec)

 mysql> 
 mysql> SHOW TRIGGERS;
 Empty set (0.00 sec)

The NEW keyword does not apply to all event types. For a DELETE query, we do not insert any row and so the NEW keyword cannot be used. For DELETE, MySQL provides an OLD keyword. Thus, if we were to create the following trigger, then it would be an error because when we delete an existing record, there is no notion of NEW record.

 mysql> SET @total_removed = 0;
 Query OK, 0 rows affected (0.00 sec)

 mysql> CREATE TRIGGER remove_quantity BEFORE DELETE ON inventory 
     -> FOR EACH ROW SET @total_removed = @total_removed + NEW.item_quantity;
 ERROR 1363 (HY000): There is no NEW row in on DELETE trigger
 mysql> 
 mysql> CREATE TRIGGER remove_quantity BEFORE DELETE ON inventory 
     -> FOR EACH ROW SET @total_removed = @total_removed + OLD.item_quantity;
 Query OK, 0 rows affected (0.08 sec)

It is possible that for some trigger logic, both action times (BEFORE/AFTER) would yield the same result with DELETE queries. For example, in the following case, after deleting a record, both variables, @total_removed_before and @total_removed_after would have the same values.

 mysql> SET @total_removed_before = 0;
 Query OK, 0 rows affected (0.00 sec)

 mysql> CREATE TRIGGER remove_quantity_before BEFORE DELETE ON inventory 
     -> FOR EACH ROW SET @total_removed_before = @total_removed_before + OLD.item_quantity;
 Query OK, 0 rows affected (0.09 sec)

 mysql> SET @total_removed_after = 0;
 Query OK, 0 rows affected (0.00 sec)

 mysql> CREATE TRIGGER remove_quantity_after AFTER DELETE ON inventory 
     -> FOR EACH ROW SET @total_removed_after = @total_removed_after + OLD.item_quantity;
 Query OK, 0 rows affected (0.08 sec)

Another point to remember is that the NEW and OLD keyword would have different meanings when it comes to UPDATE values. The reason why they are different is that the OLD keyword would store the value before the update and the NEW keyword would store the value after the update.

Compared to other databases, MySQL's support for triggers is simpler. For example, MySQL does support not multiple triggers with the same action time and event for one table. Also, as we have seen, MySQL supports triggers only for three events: INSERT, UPDATE, and DELETE.

In general, adding triggers can lead to a slight performance penalty since every event for a given table (that has trigger) would invoke the trigger leading to a performance overhead. Wherever possible, we should consider adding these logic at the application level and this way, the database server continues to run without any performance penalty.





comments powered by Disqus