MySQL trigger initial test: When Table A inserts A new record, records with the same ID are automatically inserted in Table B. mysql trigger

MySQL trigger initial test: When Table A inserts A new record, records with the same ID are automatically inserted in Table B. mysql trigger

I used the MySQL trigger for the first time today. I'm afraid I forgot to write a blog record.

If you don't talk nonsense, add the syntax first:

1 CREATE TRIGGER trigger_name2 { BEFORE | AFTER } { INSERT | UPDATE | DELETE }3 ON tbl_name4 FOR EACH ROW5 trigger_body

The cause is: I have a person information table pers. Because there are many fields, the Chinese fields are listed and another table perscn is created. I hope that when pers inserts a record, perscn can also automatically insert a record, so that the two tables can have one-to-one correspondence. The triggers in MySQL meet this requirement. However, it takes only half a day to complete the research.

First run the Code:

1 CREATE TRIGGER t_pers_perscn2 AFTER INSERT ON pers3 FOR EACH ROW4 INSERT INTO perscn(pid, sname, oname, unic) VALUES(5 (SELECT MAX(pid) FROM pers),6 (SELECT sname FROM pers ORDER BY pid DESC LIMIT 1),7 (SELECT oname FROM pers ORDER BY pid DESC LIMIT 1),8 (SELECT unic FROM pers ORDER BY pid DESC LIMIT 1)9 );

The above code roughly says:

1 create trigger t_pers_perscn
2. After the new record is inserted in pers
3. For each record
4. Insert a record to perscn, which contains four fields. The values are (
5 (pid ),
6 (sname ),
7 (oname ),
8 (unic)
9 );

Because you need to obtain the ID of the insert record in the pers table (the Field name in this table is pid), you cannot use LAST_INSERT_ID () at first. After thinking about MAX (), we can get the latest inserted pid value, but other values in perscn cannot be obtained using SELECT sname FROM pers WHERE pid = MAX (pid, it seems that the MAX () function cannot be used in the WHERE statement ...... At last, we can only use a stupid method to sort the pid in descending order, and then use LIMIT to LIMIT one record. In this way, we can also obtain the same effect as the MAX () function. No way, it's so stupid ......

Return to the top syntax, which roughly means:

1 create trigger name
2. Before or after the INSERT, UPDATE, or DELETE action
3 ON the table for which the above actions are applied
4 for each row (required statement, copy it)
5. the SQL statement triggered by the above actions (that is, the normal SQL operation statement)

The trigger_body in step 1 can only write one row. If there are multiple operation statements, you must first use DELIMITER to temporarily change the statement Terminator, and then use BEGIN... END... statement to write the operation statement (because BEGIN... END... you must use a semicolon (;) to mark the end of a row of statements. Therefore, you must use DELIMITER ). For details about how to change DELIMITER, refer to the official document example:

1 mysql> delimiter //2 mysql> CREATE PROCEDURE dorepeat(p1 INT)3     -> BEGIN4     ->   SET @x = 0;5     ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;6     -> END7     -> //8 Query OK, 0 rows affected (0.00 sec)9 mysql> delimiter ;

A trigger is really a good thing, an automation artifact ...... Study and explore again in another day ~~~