About Mysql triggers and Mysql triggers

About Mysql triggers and Mysql triggers

First, the test version is Mysql 5.6.

Then let's look at the trigger syntax.

CREATE [DEFINER = {user| CURRENT_USER}] TRIGGERtrigger_name    trigger_time trigger_eventONtbl_nameFOR EACH ROWtrigger_bodytrigger_time: {BEFORE | AFTER}trigger_event: {INSERT | UPDATE | DELETE}
This is the information found in the online document. In fact, the syntax is quite simple. Chestnut on the bottom
First, create two test tables.

CREATE TABLE `test` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Col1` varchar(50) DEFAULT NULL,  `Col2` varchar(50) DEFAULT NULL,  `Col3` int(11) DEFAULT NULL,  `Col4` float DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;CREATE TABLE `testTri` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Col1` varchar(50) DEFAULT NULL,  `Col2` varchar(50) DEFAULT NULL,  `Col3` int(11) DEFAULT NULL,  `Col4` float DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Because it is only used for testing, I just created the structure and created an auto-incrementing primary key.

Then create a trigger in the testTri table (the simplest trigger playing is like this, let's simply explain (into _ tables))

drop trigger if exists TR_testTri;delimiter //create trigger TR_testTri after insert on testTri for each rowbegin     insert into test(Col1,Col2,Col3,Col4) values (1,2,3,5);end//delimiter ;

 

Then add a row of record to testTri. Obviously, the test table also adds a row of record.

Insert into testTri (Col1, Col2, Col3, Col4) values (1, 2, 4 );

Both test and testTri write a row of records.
The normal situation is written here. Of course, this is true for the Insert statement, and for the Update/Delete statement, and for the Before/After statement in mysql, it is only about the execution sequence.


Then I tested the concentration.
1. Trigger recursion. Create a trigger in the table testTri. The trigger content is to write a record to testTri. Perform an experiment

drop trigger if exists TR_testTri;delimiter //create trigger TR_testTri after insert on testTri for each rowbegin     insert into TR_testTri(Col1,Col2,Col3,Col4) values (1,2,3,5);end//delimiter ;

 

It is successfully created, and there is no syntax problem. Add a record

Insert into testTri (Col1, Col2, Col3, Col4) values (1, 2, 4 );

Then, an error was reported.
Insert into testTri (Col1, Col2, Col3, Col4) values (1, 2, 4) Error Code: 1442. can't update table 'testtri' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 0.047 sec

Therefore, it is verified that you cannot recursion yourself.


Two tables are cyclically recursive, and a trigger is created in both test and testTri to write data to each other.

drop trigger if exists TR_testTri;delimiter //create trigger TR_testTri after insert on testTri for each rowbegin     insert into test(Col1,Col2,Col3,Col4) values (1,2,3,5);end//delimiter ;drop trigger if exists TR_testTri2;delimiter //create trigger TR_testTri2 after insert on test for each rowbegin     insert into testTri(Col1,Col2,Col3,Col4) values (1,2,3,6);end//delimiter ;

 

It is successfully created, and there is no syntax problem. Add a record

Insert into testTri (Col1, Col2, Col3, Col4) values (1, 2, 4 );

Then I reported an error.

Insert into testTri (Col1, Col2, Col3, Col4) values (1, 2, 4) Error Code: 1442. can't update table 'testtri' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. 0.047 sec

It turns out that such circular references are not allowed.


This is a simple experiment. It proves that mysql is not suitable for playing too advanced logic in the trigger, otherwise it really does not know how the error occurs. But relatively speaking, it is easier to control the program, and it is also a friendly side ~
PS: I am not very familiar with Mysql ~ For more information, see