MySQL triggers are used in PHP projects for information backup, recovery, and emptying

Case: The PHP backend code allows the employee's information to be deleted, the deleted employee information will be restored (similar to recovering employee information from the Recycle Bin), and the deleted employee can also be emptied (similar to the function of emptying the reply station).

Idea: To have an employee table, but also to have an employee backup table; Back up, use triggers, import the information from the employee table into the backup table before clicking the Delete button to perform the delete function, restore the backup table, use a trigger to delete the data from the backup table, This data is imported into the employee table at the same time, emptied, using the truncate method, emptying the data from the backup table and freeing the memory, and the method does not invoke the trigger for data deletion. Words not much, directly on the dry.

First step: Build table, employee table, employee Backup table.

1 CREATE TABLE' Employee ' (2' ID 'int( One) not NULLAuto_increment,3' Denumber 'varchar(255)DEFAULT '0',4' IDNumber 'varchar(255)DEFAULT '0',5' Worknumber 'varchar(255)DEFAULT '1',6' PWD 'varchar(255)DEFAULT NULL,7' Emname 'varchar(255)DEFAULT '0',8' Tel 'varchar(255)DEFAULT '0',9' Salary 'int(255)DEFAULT '0',Ten' Entrytime 'varchar(255)DEFAULT '0', One' Orderpaixu 'int(255)DEFAULT '1', A   PRIMARY KEY(' id ') -) ENGINE=MyISAM auto_increment= - DEFAULTCHARSET=UTF8 -This is the employee table.

CREATE TABLE' Employeebackup ' (' ID ')int( One) 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=MyISAMDEFAULTCHARSET=UTF8 Employee Backup table, one more field deletetime, in order to record the deletion time

The second step: backup, to the Employee table building trigger (information about the trigger can view my other blog http://www.cnblogs.com/liebagefly/p/7517998.html), 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 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 Backend method, I use the framework is YII2.

 Public function Actionemployeedel ($id)    {        Employee:: FindOne ($id)- >Delete ();         return $this->redirect ([' Employeemanage ']);    }

The third step: restore, restore the deleted information, use a trigger on the backup table, delete the data from the backup table, delete the data and import it into the employee table.

Trigger SQL code:

CREATE Trigger 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 the backup, sometimes to clear the function, using the truncate method, the data in the backup table is completely emptied, and free memory, and this method of data deletion will not invoke the trigger.

The original wording of SQL is called in the background of YII2, and all deleted users are emptied.

Actiondropemployeedel ()
{
    Yii::$appdb->createcommand (' TRUNCATE table Employeebackup ')
->execute ();
$this->redirect ([' Recoveremployee ']);
}

The above is a simple use of MySQL triggers in small projects.

Original link: http://www.cnblogs.com/liebagefly/p/7820324.html

MySQL triggers are used in PHP projects for information backup, recovery, and emptying