This article mainly introduces the triggers in MySQL, including the use of triggers to add, update, delete users and other operations, the need for friends can refer to the
In many cases, it's better to be clear than to think. The program should be designed according to the scene, not blindly relying on experience, of course, this is also a new experience!
Demand is the company's several sets of public systems through the mailbox username and password Authentication, just remember a pair of username password, simple, only allow users to modify the password on the Extmail Web page! In the Forum certification, because of the complexity of the forum, thought of the following several programs:
Through the OAuth2.0 to do authentication, or write their own interface, the problem is only heard, did not play!
When the mailbox is modified, it is also submitted to the Forum database. Unfortunately not extmail Perl code, and modify the code too much work!
Has previously put the online Mailbox user table synchronized to the intranet, you can do database replication, the forum and Mailbox database fields have greater access, or to change a lot of code!
Use triggers to update the user table for the forum database!
After careful consideration of the previous three methods, within the capabilities, has been written in PHP Forum certification to adapt to the mailbox of the salt-adding authentication method, and test the import mailbox username and password authentication. The Forum database field type has been modified, part of the back-end code! The final step is to automatically synchronize the user table changes in the mailbox database to the user table of the Forum database.
In the discussion of database replication, found that the last trigger is the most feasible, by setting the default value for most of the fields in the Forum user table, to add only the changes in the account, password and mail three fields can be completed on the forum user operation, the following is based on the actual situation of the MySQL trigger!
Trigger Add User
|1 2 3 4 5 6 7 8 9 10||Use Extmail; DELIMITER//CREATE trigger Add_bbsuser after insert on Extmail.mailbox for each row begin inserts into Xiuno_bbs.bbs_user ( Username,password,email) VALUES (new.name,new.password,new.username); End//DELIMITER;|
Triggers update users
|1 2 3 4 5 6 7 8 9 10||Use Extmail; DELIMITER//CREATE trigger Update_bbsuser after update on extmail.mailbox for each row begin update Xiuno_bbs.bbs_user SE T Username=new.name, Password=new.password where email=new.username; End//DELIMITER;|
Triggers delete users
|1 2 3 4 5 6 7 8 9||Use Extmail; DELIMITER//CREATE trigger Delete_bbsuser after delete on extmail.mailbox for each row begin delete from Xiuno_bbs.bbs_us ER where email=old.username; End//DELIMITER;|
The pit I stepped on
Just start writing triggers, from the Internet to find the command can not use, completely helpless to realize that the trigger and the database is bound, must first use Extmail to trigger the Extmail database, in order to the operation of the library to do the corresponding trigger, then there is no problem! I'm a MySQL white!