Mysql triggers are used in PHP projects to back up, restore and clear information

Examples:

Through the PHP background code, you can delete employee information, restore deleted employee information (similar to restoring employee information from the recycle bin), and clear deleted employees (similar to the function of clearing the reply station).

Ideas:

There must be an employee table and an employee backup table; for backup, use triggers to import the information in the employee table into the backup table before clicking the delete button to execute the delete function, thus achieving the backup effect; for recovery, 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; for clearing, use the truncate method to completely clear the data in the backup table and release memory, and this method will not call triggers for data deletion. Without further ado, let me get straight to the point.

Step 1: Create tables, employee table, and employee backup table.

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

This is the employee list

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 backup table has an additional field called deletetime to record the deletion time.

Step 2: Back up and create a trigger for the employee table (for information about triggers, you can view my other blog http://www.cnblogs.com/liebagefly/p/7517998.html), and import the information in the employee table into the backup table before clicking the delete button to execute the delete function.

Trigger sql code:

CREATE trigger deletesemployee before delete on employee
for each ROW
begin 
 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']);
 }

Step 3: Restore, restore the deleted information, use triggers on the backup table, delete the data in the backup table, and import the data into the employee table while deleting it.

Trigger sql code:

CREATE trigger deletesemployeebackup before delete on employeebackup
for each ROW
begin 
  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 a clearing function is also required. Use the truncate method to completely clear the data in the backup table and release memory. This method will not call triggers for data deletion.

The original way of calling SQL in the background of Yii2 is to clear all deleted users.

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

The above is a simple usage of MySQL trigger in a small project.