MySQL-TRIGGER

by

1. What is a trigger?

  • Definition: Like stored procedures, MySQL triggers are programs embedded in MySQL, serving as powerful tools for data management. They are triggered and activated by operations performed on data tables. For example, performing operations (INSERT, DELETE, or UPDATE) on the `student` table will activate its execution.
  • Function: Triggers are closely related to data tables and are primarily used to protect the data within those tables. Especially when multiple tables are interconnected, triggers can ensure data consistency , logging  , and  data validation across different tables .

2. Advantages and disadvantages of triggers

The advantages of triggers are as follows:

  • Triggers are executed automatically; they are executed immediately after the data in the table associated with the trigger is modified.
  • Triggers can perform more complex checks and operations than FOREIGN KEY constraints and CHECK constraints.
  • Triggers can enable cascading changes to table data, which to some extent ensures data integrity .

The disadvantages of triggers are as follows:

  • Business logic implemented using triggers is difficult to locate when problems occur, especially when multiple triggers are involved, which makes later maintenance difficult .
  • Excessive use of triggers can easily disrupt the code structure and increase the complexity of the program .
  • If the amount of data to be changed is large, the execution efficiency of the trigger will be very low.

3. Types of triggers

  • In practical use, MySQL supports three types of triggers: INSERT triggers,  UPDATE triggers, and DELETE triggers.
  • The aliases OLD and NEW are used to reference the changed records in a trigger, similar to other databases. Currently, triggers only support row-level triggering, not statement-level triggering. Trigger Types
NEW and OLD 

type triggers: 

NEW indicates data that will be added or has already been added; OLD 

type triggers 

indicate data before modification, NEW indicates data that will be modified or has already been modified; 

DELETE type triggers 

OLD indicate data that will be deleted or has already been deleted.
3.1 INSERT trigger
  • Triggers that respond before or after the execution of an INSERT statement. When using INSERT triggers, the following points should be noted:
    1. Further operations are performed within the INSERT trigger code, where a virtual table named NEW (case-insensitive) can be referenced to access the inserted rows.
    2. In a BEFORE INSERT trigger, the value in NEW can also be updated, meaning that the inserted value can be changed (provided that the corresponding operation permission is available).
    3. For the AUTO_INCREMENT column, NEW contains a value of 0 before the INSERT operation, and will contain a new automatically generated value after the INSERT operation.
3.2 UPDATE Triggers
  • Triggers that respond before or after the execution of an UPDATE statement. When using UPDATE triggers, the following points should be noted:
    1. Within the UPDATE trigger code, you can reference a virtual table named NEW (case-insensitive) to access the updated values.
    2. Within the UPDATE trigger code, a virtual table named OLD (case-insensitive) can be referenced to access values ​​before the UPDATE statement is executed.
    3. In a BEFORE UPDATE trigger, the value in NEW may also be updated, meaning that it is permissible to change the value that will be used in the UPDATE statement (provided that the corresponding operation permissions are available).
    4. All values ​​in OLD are read-only and cannot be updated.
    Note: When a trigger is designed to update the triggering table itself, only triggers of type BEFORE can be used; triggers of type AFTER will not be allowed.
3.3 DELETE trigger
  • A trigger that responds before or after the execution of a DELETE statement. The following points should be noted when using DELETE triggers:
    1. Within the DELETE trigger code, you can reference a virtual table named OLD (case-insensitive) to access the deleted rows.
    2. All values ​​in OLD are read-only and cannot be updated.

In general, MySQL handles errors as follows when triggers are used.

  1. For transactional tables, if the triggering procedure fails, and the entire statement fails as a result, all changes made by that statement will be rolled back. For non-transactional tables, such a rollback cannot be performed; any changes made before the statement fails will remain valid.
  2. If the BEFORE trigger fails, MySQL will not perform the operation on the corresponding row.
  3. If an error occurs during the execution of the BEFORE or AFTER trigger, the entire statement that calls the trigger will fail.
  4. MySQL will only execute the AFTER trigger if both the BEFORE trigger and the row operation have been successfully executed.

4. Trigger-related syntax

4.1 Creating a Trigger
  • In MySQL 5.7, triggers can be created using the CREATE TRIGGER statement, with the following syntax:
CREATE <Trigger Name> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <Table Name> FOR EACH Row <Trigger Sql>
  1. Trigger name

The trigger’s name must be unique within the current database. If the trigger is to be created in a specific database, the database name should be prefixed to the name.

  1. INSERT | UPDATE | DELETE

Trigger event, used to specify the type of statement that activates the trigger.

Note: The execution times for the three triggers are as follows.

  • INSERT: Activates the trigger when a new row is inserted into the table. For example, the BEFORE trigger of INSERT can be activated not only by the MySQL INSERT statement but also by the LOAD DATA statement.
  • DELETE: Activates a trigger when a row of data is deleted from a table, such as with the DELETE and REPLACE statements.
  • UPDATE: Activates a trigger when a row of data in a table is changed, such as with an UPDATE statement.
  1. BEFORE | AFTER

BEFORE and AFTER specify when a trigger is fired, indicating whether the trigger fires before or after the statement that activated it. Use the BEFORE option to verify if new data meets a condition; use the AFTER option to perform several or more changes after the statement that activated the trigger has been executed.

  1. Table name

The name of the table associated with the trigger. This table must be a persistent table; triggers cannot be associated with temporary tables or views. The trigger is activated only when the triggering event occurs on this table. A single table cannot have two triggers with the same trigger time and event. For example, a table cannot have two BEFORE UPDATE triggers simultaneously, but it can have one BEFORE UPDATE trigger and one BEFORE INSERT trigger, or one BEFORE UPDATE trigger and one AFTER UPDATE trigger.

  1. Trigger body

The trigger action body contains the MySQL statements to be executed when the trigger is activated. To execute multiple statements, use the BEGIN…END compound statement structure.

  1. FOR EACH ROW

This generally refers to row-level triggering, where the trigger action is activated for each row affected by the triggering event. For example, when using the INSERT statement to insert multiple rows of data into a table, the trigger will execute the corresponding trigger action for each inserted row.

  • Case 1: Insert Data Trigger
create trigger tb_user_insert_trigger
after insert on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
	(null, 'insert', now(), new.id, concat('The inserted data is:id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',profession=', NEW.profession));
end;
  • Case 2: Modifying the trigger
create trigger tb_user_update_trigger
after update on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
	(null, 'update', now(), new.id,concat('Data before update : id=',old.id,',name=',old.name, ', phone=',
	old.phone, ', email=', old.email, ', profession=', old.profession,' | Updated data: 					id=',new.id,',name=',new.name, ', phone=',NEW.phone, ', email=', NEW.email, ', profession=', 		NEW.profession));
end;
  • Case 3: Deleting a trigger
create trigger tb_user_delete_trigger
after delete on tb_user for each row
begin
	insert into user_logs(id, operation, operate_time, operate_id, operate_params) VALUES
	(null, 'delete', now(), old.id,concat('Data before deletion: id=',old.id,',name=',old.name, ', phone=',
	old.phone, ', email=', old.email, ', profession=', old.profession));
end;
4.2 Viewing Triggers
  • In MySQL, you can use the SHOW TRIGGERS statement to view basic information about triggers. The syntax is as follows:
SHOW TRIGGERS;
  • In MySQL, all trigger information is stored in the `triggers` table of the `information_schema` database. It can be viewed using the `SELECT` command, with the following syntax:
SELECT * FROM information_schema.triggers WHERE trigger_name= 'Trigger Name';
  • View all triggers:
DROP TRIGGER [ IF EXISTS ] [Table Name] <Trigger Name>
4.3 Deleting the trigger
  • The DROP TRIGGER statement can be used to delete triggers that are already defined in MySQL. DROP TRIGGER [ IF EXISTS ] [Table Name] <Trigger Name>