MySQL Trigger preliminary: When a table inserts a new record, automatically inserts the same ID record in table B

Today the first time with the MySQL trigger, afraid to forget, hurriedly write a blog record.

Nonsense not to say, first on the grammar:

1 CREATE TRIGGER trigger_name 2 | INSERT | UPDATE | DELETE }3 on tbl_name4 for each ROW5 Trigger_body

The cause of the matter is this: I have a personnel information sheet pers. Because of a lot of fields, the Chinese text paragraph is a single out, another table PERSCN. I hope that when Pers inserts a record, PERSCN can also automatically insert a record, which will ensure that the records one by one of the two tables correspond. The triggers in MySQL just meet this requirement. But it was also studied for half a day to finally fix it.

First on the code:

1 CREATE TRIGGERT_PERS_PERSCN2AfterINSERT  onPers3  forEach ROW4 INSERT  intoPERSCN (PID, sname, Oname, UNIC)VALUES(5(SELECT MAX(PID) fromPers),6(SELECTSname fromPersORDER  byPidDESCLIMIT1),7(SELECTOname fromPersORDER  byPidDESCLIMIT1),8(SELECTUnic fromPersORDER  byPidDESCLIMIT1)9);

The above code basically says:

1 Creating a Trigger T_PERS_PERSCN
2 after inserting a new record in Pers
3 for each record
4 Insert a record into PERSCN, containing 4 fields with values (
5 (PID),
6 (sname),
7 (Oname),
8 (UNIC)
9);

Because you need to get the ID of the inserted record in the Pers table (the field named PID in this table), it is not feasible to start with last_insert_id (). Later thought of MAX (), but can get the latest inserted PID value, but the other values in PERSCN can not be used in the SELECT sname from pers WHERE pid = MAX (PID); To fetch, it appears that the MAX () function is not available in the WHERE statement ... Finally, you can only use the stupid method, the PID is sorted in descending order, and then limit 1 records with limit, so you can also get the same effect as the MAX () function. No way, just so stupid ...

Back to the top of the grammar, the general meaning is:

1 CREATE TRIGGER Trigger name
2 before or after an INSERT, update, or delete action
3 on the table to which the above actions function
4 for each ROW (specify the statement, copy it)
SQL statement triggered by more than 5 actions (i.e. normal SQL action statement)

Among them, the 5th step of Trigger_body generally can only write one line. If you have more than one action statement, you need to use DELIMITER to temporarily change the next statement terminator, and then use BEGIN ... END ... Statement to write an operation statement (because the BEGIN ... END ... You must use the half-width semicolon ";" to identify the end of a line of statements, so you need to use DELIMITER). For changes to DELIMITER, see an example of an official document:

1Mysql>Delimiter//2Mysql> CREATE PROCEDUREDorepeat (P1INT)3      - BEGIN4      -   SET @x = 0;5      -REPEATSET @x = @x + 1; UNTIL@x >P1ENDREPEAT;6      - END7      - //8Query OK,0Rows Affected (0.00sec)9Mysql>delimiter;

The trigger is a good thing, the automatic artifact ah ... Another day to study the research, excavation and excavation ~ ~ ~

MySQL Trigger preliminary: When a table inserts a new record, automatically inserts the same ID record in table B