Shulou(Shulou.com)06/01 Report--

The following is about the PHP project through Mysql triggers to do information backup, recovery and emptying tutorials, the secret of the text is close to the topic. So, forget the gossip, let's go straight to the following, and I'm sure you'll benefit from reading the tutorial on backing up, restoring and emptying information through Mysql triggers in the PHP project.

Train of thought:

There should be an employee table and an employee backup table; backup, use triggers to import the information from the employee table into the backup table before clicking the delete button to perform the delete function, so as to achieve the effect of backup; restore, use triggers on the backup table to delete the data in the backup table, and import the data into the employee table at the same time Emptying, using the truncate method, completely empties the data in the backup table and frees memory, and this method for data deletion does not call triggers. If you don't say much, go straight to the practical information.

The first step: build the table, employee table, employee backup form.

CREATE TABLE `employee` (`id` int (11) NOT NULL AUTO_INCREMENT, `denumber` varchar (255) DEFAULT '0employees, `idnumber` varchar (255) DEFAULT' 0requests, `worknumber`varchar (255) DEFAULT '1numbers, `pwd` varchar (255) DEFAULT NULL, `emname` DEFAULT' 0employees, `tel`varchar (255) DEFAULT '0questions, `salary`int (255) DEFAULT' 0requests, `entrytime` varchar (255) DEFAULT '0cycles, `orderpaixu` DEFAULT' 1numbers, PRIMARY KEY (`id`) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8)

This is the employee form.

CREATE TABLE `employeebackup` (`id` int (11) NOT NULL, `denumber` varchar (255) DEFAULT NULL, `idnumber` varchar (255) DEFAULT NULL, `worknumber` varchar (255) DEFAULT NULL, `pwd` varchar (255) DEFAULT NULL, `emname` varchar (255) DEFAULT NULL, `tel`varchar (255) DEFAULT NULL, `salary`int (255) DEFAULT NULL, `entrytime` varchar (255) DEFAULT NULL, `orderpaixu` int (255) DEFAULT NULL, `deletetime` datetime DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=MyISAM DEFAULT CHARSET=utf8

The employee backs up the table with an additional field deletetime to record the deletion time

Step 2: back up, create a trigger for the employee table (see my other blog http://www.cnblogs.com/liebagefly/p/7517998.html for information about the trigger), and import the information from the employee table into the backup table before clicking the delete button to perform the delete function.

Trigger sql code:

CREATE trigger deletesemployee before delete on employeefor each ROWbegin insert into employeebackup (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu,deletetime) values (OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu,NOW ()); end

Php background method, the framework I use is yii2.

Public function actionEmployeedel ($id) {Employee::findOne ($id)-> delete (); return $this- > redirect (['employeemanage']);}

The third step: restore, restore the deleted information, use the trigger on the backup table, delete the data in the backup table, and import the data into the employee table at the same time.

Trigger sql code:

CREATE trigger deletesemployeebackup before delete on employeebackupfor each ROWbegin insert into employee (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu) values (OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu); end

Php code

Public function actionRecoveremployeedel ($id) {Employeebackup::findOne ($id)-> delete (); return $this- > redirect (['recoveremployee']);}

In addition to backup, sometimes you have to do emptying, using the truncate method to completely empty the data in the backup table and free memory, and this method for data deletion does not call a trigger.

The original writing method of sql is called in the background of yii2, which empties all deleted users.

Public function actionDropemployeedel () {Yii::$app- > db- > createCommand ('truncate table employeebackup')-> execute (); return $this- > redirect ([' recoveremployee']);}

Is there anything you don't understand about the information backup, recovery and emptying of tutorials through Mysql triggers in the above PHP project? Or if you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.