mysql trigger creation and usage examples

This article describes how to create and use MySQL triggers. Share with you for your reference, the details are as follows:

What is a trigger

• Triggers are used to "automatically" perform some actions after/before certain actions. (For example, if new student information is inserted, the number of students should be modified in the class table).
• After the insert delete update trigger is set, executing the insert delete update operation will automatically trigger the set content.
• A table can have a maximum of 6 triggers (3*2, three operations*two times (before|after)).

Create a trigger

grammar:

create trigger trigger name before|after event on table name for each row trigger statement;

• The recommended trigger name is trigger_xxx, which is easy to distinguish. Trigger names cannot be repeated .
• before|after represents the execution time of the trigger statement. If it is before, the trigger statement is executed before the insert, delete, or update operation; after means after.
• An event is one of the insert, delete, and update operations.
• for each row is a trigger that will execute the corresponding operation on any record.
• A trigger statement is a statement that is executed when a trigger is fired.

Explanation of keywords in MySQL creation syntax:

Mysql trigger usage example:

1. MySQL trigger Insert triggers update of the same table:

Below we have a table " tmp1 ", tmp1 table has two integer fields: n1, n2. We need to use triggers to automatically set the value of the n2 field to 5 times the value of the n1 field when inserting records into tmp.
Create a test table and trigger:

-- Create a test table drop table if exists tmp1;
 
create table tmp1 (n1 int, n2 int);
 
-- Create trigger DELIMITER $
 
drop trigger if exists tmp1_insert$
 
create trigger tmp1_insert
 
before insert on tmp1
 
for each row
 
begin
 
  set new.n2 = new.n1*5;
 
end$
 
DELIMITER ;

Test trigger update effect:

mysql> insert tmp1(n1) values(18);
 
Query OK, 1 row affected (0.01 sec)
 
mysql> insert tmp1(n1) values(99);
 
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from tmp1;
 
+------+------+
 
| n1 | n2 |
 
+------+------+
 
| 18 | 90 |
 
| 99 | 495 |
 
+------+------+
 
2 rows in set (0.00 sec)

2. MySQL trigger Update triggers to update another table:

Below are two tables tmp1 and tmp2, both of which have the same field name . When a trigger is used to update the name of a table, the name of another table will also be updated.
Create a test table and trigger:

-- Create a test table and insert test data drop table if exists tmp1;
 
drop table if exists tmp2;
 
create table tmp1 (id int, name varchar(128)) default charset='utf8';
 
create table tmp2 (fid int, name varchar(128)) default charset='utf8';
 
insert into tmp1 values(1, '爱E族');
 
insert into tmp2 values(1, '爱E族');
 
-- Create trigger DELIMITER $
 
drop trigger if exists tmp1_update$
 
create trigger tmp1_update
 
after update on tmp1
 
for each row
 
begin
 
  update tmp2 set name=new.name where fid=new.id;
 
end$
 
DELIMITER ;

Test trigger update effect:

mysql> select * from tmp1;
 
+------+---------+
 
| id | name |
 
+------+---------+
 
| 1 | Love E tribe |
 
+------+---------+
 
1 row in set (0.00 sec)
 
mysql> select * from tmp2;
 
+------+---------+
 
| fid | name |
 
+------+---------+
 
| 1 | Love E tribe |
 
+------+---------+
 
1 row in set (0.00 sec)
 
mysql> update tmp1 set name='aiezu.com' where id=1;
 
Query OK, 1 row affected (0.00 sec)
 
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> select * from tmp1;
 
+------+-----------+
 
| id | name |
 
+------+-----------+
 
| 1 | aiezu.com |
 
+------+-----------+
 
1 row in set (0.00 sec)
 
mysql> select * from tmp2;
 
+------+-----------+
 
| fid | name |
 
+------+-----------+
 
| 1 | aiezu.com |
 
+------+-----------+
 
1 row in set (0.00 sec)

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.