Detailed explanation of the basic usage of MySQL triggers [create, view, delete, etc.]

This article uses examples to illustrate the basic usage of MySQL triggers. Share with you for your reference, the details are as follows:

1. MySQL trigger creation:

1. MySQL trigger creation syntax:

CREATE [DEFINER = { 'user' | CURRENT_USER }]　
TRIGGER trigger_name
trigger_time trigger_event
ON table_name
FOR EACH ROW
[trigger_order]
trigger_body

2. Explanation of keywords in MySQL creation syntax:

3. OLD, NEW in the trigger execution statement content (trigger_body):

In trigger_body, we can use NEW to indicate new rows to be inserted (equivalent to MS SQL's INSERTED ), and OLD to indicate old rows to be deleted (equivalent to MS SQL's DELETED ). Get their field contents from OLD and NEW for easy use in trigger operations. The following is the relationship between whether the corresponding event supports OLD and NEW:

Since UPDATE is equivalent to deleting the old row (OLD) and inserting the new row (NEW), UPDATE supports both OLD and NEW.

4. MySQL delimiter (DELIMITER):

MySQL uses ";" as a delimiter by default, and the SQL statement will be submitted when it encounters ";". There may be multiple ";" characters in our trigger. In order to prevent the trigger creation statement from being submitted prematurely, we need to temporarily modify the MySQL delimiter and change the delimiter back after creation. Use DELIMITER to modify the delimiter as follows:

DELIMITER $
... --trigger creation statement;
$ --Submit the creation statement;
DELIMITER ;

2. Advanced MySQL trigger creation:

1. Using variables in MySQL triggers:

In MySQL triggers, variables are preceded by '@' and can be used directly without definition:

--Variable direct assignment set @num=999;
 
-- Use the data from the select statement to assign a value, brackets are required:
set @name =(select name from table);

2. Use if statement in MySQL trigger to make conditional judgment:

-- Simple if statement:
set sex = if (new.sex=1, 'male', 'female');
 
--Multiple conditional if statements:
if old.type=1 then
  update table ...;
elseif old.type=2 then
  update table ...;
end if;

3. MySQL view trigger:

You can view the triggers using " show triggers; ". Since the triggers created by MySQL are saved in the triggers table in the information_schema library, you can also view the triggers by querying this table:

-- View the triggers through the information_schema.triggers table:
select * from information_schema.triggers;
 
--mysql shows triggers to view the current database;
 
-- mysql shows triggers from aiezu for the specified database "aiezu";

4. MySQL delete trigger:

1. You can use drop trigger to delete the trigger:

drop trigger trigger_name;

2. Check whether the trigger exists before deleting:

drop trigger if exists trigger_name

5. Examples of Msql trigger usage:

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 n2 field to 5 times the value of 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 name of a table, 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.