Detailed explanation of MySQL database triggers

1 Introduction

This article summarizes triggers in MySQL. Starting from the concept of triggers, this article introduces how to create, use, and delete triggers with examples.

2 Trigger Introduction

MySQL triggers, like stored procedures, are programs embedded in MySQL. A trigger is an event that triggers an action, including INSERT, UPDATE, and DELETE. If a trigger is defined, when the database executes these statements, the trigger will be activated to perform the corresponding operation. The trigger is a command database object related to the table. When a specific event occurs on the table, the object will be activated.

A trigger is a special stored procedure. The difference is that a stored procedure must be called using the call statement, but a trigger does not need to be called using the call statement, nor does it need to be started manually. As long as a predefined event occurs, the trigger will be automatically called by MySQL. Triggers can query other tables and can contain complex SQL statements.

3 Create a trigger

(1) Create a trigger with only one execution statement

The grammatical structure is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt

trigger_name: user-defined trigger name;

trigger_time: identifies the trigger event, which can be specified as before (executed before the time occurs) or after (executed after the event occurs);

trigger_event: identifies the trigger event, including INSERT, UPDATE, and DELETE;

table_name: the table on which the trigger is created;

trigger_stmt: trigger execution statement.

(2) Create a trigger with multiple execution statements

The grammatical structure is as follows:

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW

 BEGIN

  Statement execution list END

When a trigger has at least one execution statement, multiple execution statements need to be wrapped with BEGIN and END, which respectively indicate the start and end of the entire code block.

To demonstrate the trigger operation, let's create three data tables:

create table tb_student(

   id int PRIMARY key auto_increment,

   name varchar(10)

);

create table tb_before_trigger(

   id int PRIMARY key auto_increment,

   num int ,

   time_now datetime NULL DEFAULT CURRENT_TIMESTAMP

);

create table tb_after_trigger(

   id int PRIMARY key auto_increment,

   num int ,

   time_now datetime NULL DEFAULT CURRENT_TIMESTAMP

);

Example 1: Create a trigger named before_trigger. This trigger will be triggered before each insert operation is performed on the tb_student table. When triggered, a record containing the total number of records in the tb_student table will be inserted into the before_trigger table.

delimiter //

create trigger before_trigger before insert

   on tb_student for each row

   begin

     insert into tb_before_trigger (num) select count(*) from tb_student;

   end

   //

delimiter ;

Example 2: Create a trigger named after_trigger, which will be triggered before each insert operation is performed on the tb_student table. When triggered, a record containing the total number of records in the tb_student table will be inserted into the before_trigger table.

delimiter //

create trigger after_trigger after insert

   on tb_student for each row

   begin

     insert into tb_after_trigger (num) select count(*) from tb_student;

   end

   //

delimiter ;

Let's test the triggers created in Example 1 and Example 2 and insert a piece of data into the tb_student table (there are no records in the first three tables):

insert into tb_student (name) values('zhangsan');

After inserting, view the data in the three tables:

tb_student table:

tb_before_trigger table:

tb_after_trigger table:

It can be seen that after the insert operation is executed in the tb_student table, the other two tables also update records respectively. The num value of the tb_before_trigger table is 0, which proves that the record was inserted before the insert operation was executed in tb_student; the num value of the tb_after_trigger table is 1, which proves that the record was inserted after the insert operation was executed in tb_student - this is the difference between before and after.

For other conditional triggers, the usage is similar to Example 1 and Example 2, which will not be demonstrated in this article.

4 View triggers

(1) show triggers statement

You can use the show triggers statement to view the triggers created in Example 1 and Example 2:

show triggers;

Output:

(2) View triggers in the triggers table

All triggers stored in the MySQL database in the triggers table of the information_schema database can be viewed through the query statement:

select * from information_schema.triggers where trigger_name = 'before_trigger';

Output:

When no query condition is specified, all trigger information is viewed.

5. Delete trigger

Use the DROP TRIGGER statement to delete a trigger. The basic syntax structure is as follows:

DROP TRIGGER [schema_name] trigger_name

Among them, schema_name indicates the database name and is an optional parameter. If it is omitted, it means deleting the trigger from the current database.

Example 3: Delete the trigger before_trigger created in Example 1

drop trigger before_trigger;

6 Conclusion

At certain times, triggers can be the icing on the cake, but triggers are not very efficient, so try to use them as little as possible.

Author: Ao Chen

Github: https://github.com/ChenHuabin321

The above is a detailed explanation of the triggers of the MySQL database. For more information about MySQL triggers, please pay attention to other related articles on 123WORDPRESS.COM!