Introduction to the trigger _ MySQL in MySQL

This article mainly introduces the triggers in MySQL, including using triggers to add, update, and delete users. if you need them, you can refer to the following: in many cases, it is better to understand what you do. The solution should be designed based on the scenario, rather than relying on experience blindly. of course, this is a new experience!

The requirement is to make several public systems of the company pass the mailbox user name and password authentication. you only need to remember a pair of user name and password. Simply put, you can only change the password on the ExtMail web page! During Forum authentication, due to the complexity of the forum, I thought of the following solutions:

I have only heard of the authentication through OAuth2.0 or write the interface myself. I have never played it!

When the mailbox is modified, it is also submitted to the forum database. Unfortunately, it's not ExtMail's Perl code, and it's too much work to modify the code!

Previously, the online mailbox user table has been synchronized to the intranet and can be used for database replication. the database fields in the forum and mailbox are greatly different. You still need to change a lot of code!

Use a trigger to update the user table of the forum database!

After careful consideration of the preceding three methods, the Forum authentication method compiled by PHP has been changed to the salt authentication method adapted to the mailbox within the capability scope, the user name and password of the imported mailbox are verified. The field type of the forum database has been modified. some back-end codes are available! The last step is to automatically synchronize the changes to the user table of the mailbox database to the user table of the forum database.

When discussing database replication, we found that the last trigger method was the most feasible. by setting the default value for most fields in the forum user table, we can only add and modify the account, the password and email fields can be used to complete operations on forum users. The following is a MySQL trigger compiled based on the actual situation!

Trigger add user

  use extmail;  DELIMITER //  create trigger add_bbsuser  after insert on extmail.mailbox  for each row  begin  insert into xiuno_bbs.bbs_user(username,password,email) \   values (new.name,new.password,new.username);  end //   DELIMITER ;

Trigger Update User

  use extmail;  DELIMITER //  create trigger update_bbsuser  after update on extmail.mailbox  for each row  begin  update xiuno_bbs.bbs_user set username=new.name,\  password=new.password where email=new.username;  end //   DELIMITER ;

Trigger delete user

  use extmail;  DELIMITER //  create trigger delete_bbsuser  after delete on extmail.mailbox  for each row  begin  delete from xiuno_bbs.bbs_user where email=old.username;  end //   DELIMITER ;

Pitfall I step on

When I first wrote a trigger, I couldn't use any commands I found on the internet. I realized that the trigger is bound to the database and must use extmail to the extmail database to be triggered, in order to trigger operations on this database, there will be no problem later! I am a little white MySQL!