About MySQL triggers

First, test the version of Mysql 5.6.

And then look at the trigger syntax.

CREATE     user  | Current_User}     trigger_name     ] trigger_time  for each trigger_event     ROW tbl_name      trigger_body  : trigger_time  {before | After} trigger_event : {INSERT | UPDATE | DELETE}
This is the information found in the online documentation. In fact, the grammar is quite simple. Underneath the Chestnuts
First, we'll start by creating 2 test tables.

CREATE TABLE' Test ' (' ID ')int( One) not NULLauto_increment, ' Col1 'varchar( -)DEFAULT NULL, ' Col2 'varchar( -)DEFAULT NULL, ' Col3 'int( One)DEFAULT NULL, ' Col4 'float DEFAULT NULL,  PRIMARY KEY(' ID ')) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=UTF8;CREATE TABLE' Testtri ' (' ID ')int( One) not NULLauto_increment, ' Col1 'varchar( -)DEFAULT NULL, ' Col2 'varchar( -)DEFAULT NULL, ' Col3 'int( One)DEFAULT NULL, ' Col4 'float DEFAULT NULL,  PRIMARY KEY(' ID ')) ENGINE=InnoDB auto_increment=5 DEFAULTCHARSET=UTF8;

Because it's just for testing, I just built it on the structure, creating a self-adding column primary key is OK.

Then create a trigger on the Testtri table (creating the simplest trigger is like this, simple ╮ (╯_╰) ╭)

Drop Trigger if existsTr_testtri;delimiter//Create TriggerTr_testtri afterInsert  onTesttri forEach rowbegin     Insert  intoTest (COL1,COL2,COL3,COL4)Values(1,2,3,5);End//delimiter;

Then add a row of records to the top of the Testtri, and, obviously, the test table also adds a row of records.

Insert into Testtri (COL1,COL2,COL3,COL4) values (1,2,3,4);

The results test and Testtri both write a row of records.
The normal situation is written here. Of course, this is the way to play, Update/delete also play, Before/after in MySQL is only the implementation of the order problem.


And then I tested the concentration situation.
1 triggers are self-recursive, creating a trigger in table Testtri that triggers the content to write a record to Testtri. Do the experiment.

Drop Trigger if existsTr_testtri;delimiter//Create TriggerTr_testtri afterInsert  onTesttri forEach rowbegin     Insert  intoTr_testtri (COL1,COL2,COL3,COL4)Values(1,2,3,5);End//delimiter;

Insert into Testtri (COL1,COL2,COL3,COL4) values (1,2,3,4);

And then the gorgeous to error.
Insert into Testtri (COL1,COL2,COL3,COL4) VALUES (1,2,3,4) Error code:1442. Can ' t Update table ' Testtri ' in stored Function/trigger because it's already used by statement which invoked this stored Function/trigger. 0.047 sec 

So verify that you cannot recursively own yourself.


2 2 Tables loop recursively, creating a trigger in both test and Testtri to write data to each other

Drop Trigger if existsTr_testtri;delimiter//Create TriggerTr_testtri afterInsert  onTesttri forEach rowbegin     Insert  intoTest (COL1,COL2,COL3,COL4)Values(1,2,3,5);End//delimiter;Drop Trigger if existsTr_testtri2;delimiter//Create TriggerTr_testtri2 afterInsert  onTest forEach rowbegin     Insert  intoTesttri (COL1,COL2,COL3,COL4)Values(1,2,3,6);End//delimiter;

Insert into Testtri (COL1,COL2,COL3,COL4) values (1,2,3,4);

Then the gorgeous land again error.

Insert into Testtri (COL1,COL2,COL3,COL4) VALUES (1,2,3,4) Error code:1442. Can ' t Update table ' Testtri ' in stored Function/trigger because it's already used by statement which invoked this stored Function/trigger. 0.047 sec 

Proving that the circular reference is not allowed.


Did a simple experiment like this. Prove that MySQL in the trigger is not suitable for playing too high-level logic, or really do not know exactly how the error occurred. But relatively speaking, the control of the program will be easier, but also a friendly side ~
PS: In the next to MySQL is not very skilled ~ also ask you to guide

About MySQL triggers