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

In this article:

• What is a trigger
• Create a trigger
  • Single trigger statement
  • Multiple trigger statements
• View Triggers
• Deleting a trigger
• New and old record references in triggers

Release date: 2018-04-14

What is a trigger:

• Triggers are used to "automatically" perform some actions after 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:

• Syntax: 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.
  • For example: The following is a new data inserted into the user creation timetable when a new user is inserted, which is the current time.

    create trigger trigger_addUserTime 
    before
     insert 
    on user_info 
    for each row 
    insert into usercreatetime(create_time) values(now());

create table user_info(
id int primary key auto_increment,
username varchar(20) not null,
password varchar(20) not null
)

create table usercreatetime(
id int primary key auto_increment,
create_time datetime
);

create trigger trigger_addUserTime before insert on user_info for each row insert into usercreatetime(create_time) values(now());

insert into user_info(username,password) values("admin","admin888");

select * from usercreatetime;

Multiple trigger statements:

• You need to wrap multiple statements in begin end
  • for example:
• But please note that the above situation applies to non-command line mode (the above is run in navicat). If you want to execute it in the command line, you need to modify the command terminator (MySQL command uses ";" as the statement terminator by default. If we do not modify it, then typing a trigger statement and ending it with ; will consider the statement to have ended, and then an error will be reported because the statement is incomplete) [delimiter custom symbol--temporarily change the statement terminator to a specified symbol].
  • [Define the end character as $$ and use it in the trigger statement; end]

    delimiter $$ -- usually defined as $$
    create trigger trigger name before|after event on table name for each row 
    begin 
      statement; statement;
    end 
    $$
    
    delimiter ;
    -- Example delimiter $$ -- Generally defined as $$
    create trigger trigger_addUserTime23 before insert on user_info for each row 
    begin 
    insert into usercreatetime(create_time) values(now());
    insert into usercreatetime(create_time) values(now());
    end 
    $$
    delimiter ; 

  • 

View triggers:

• Use show triggers\G to view all triggers
• In the database information_schema, view the trigger by viewing the trigger table: use select * from triggers\G; [Of course, you can use the trigger name as the where condition to find data]
• View the trigger creation statement: show create trigger trigger name\G;

To delete a trigger:

• Use drop trigger trigger name; to delete the trigger

The old and new records referenced by the trigger:

• In some cases, you may need to refer to previous data, such as increasing the number of students and needing to know the previous number of students
• New is the newly inserted data, old is the original data [there is no old data when inserting, no new data when deleting, and both old and new data when updating. (This refers to the event type)]
• Use odl\new.fieldname to reference data. (Note that the data referenced is the statement that triggered the trigger (event))
• Here is an example: (When inserting a student record, get the name of the newly inserted student through new.name, and then insert new.name into another table)

  create table student(
  id int primary key auto_increment,
  name varchar(15) not null,
  gender varchar(15) not null
  );
  
  create table stu_info(
  name varchar(15) not null
  );
  
  create trigger addCount before insert on student 
  for each row 
  insert into stu_info values(new.name);
  
  insert into student(name,gender) values("lilei","male");
  select * from stu_info;

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.