How to do a trigger between two tables

How do I make a trigger between two tables?
Student Table: School number, name, age, department number, department name
List: Department number, name, name, age.

How to add a record to a student table and automatically increase it in the table. Similarly, how do I delete?

------Solution--------------------
Of course!
You can follow this blog post to http://www.cnblogs.com/nicholas_f/archive/2009/09/22/1572050.html

Hope to be successful and share your results
------Solution--------------------
I didn't do it, so I asked you to see examples of people doing it.
If I let you read the manual, wouldn't that be too much?
------Solution--------------------
Delimiter//
DROP TRIGGER IF EXISTS trigger_on_tab1//
CREATE TRIGGER TRIGGER_ON_TAB1
After INSERT on test1
For each ROW
BEGIN
Insert into Test2 (test1_id,test1_name) VALUES (new.id, new.name);
end//

A case, you can learn from the next Oh!
------Solution--------------------
The grammar is wrong. Paste out your SQL string to see.
------Solution--------------------
The statement is correct, it should be the problem of the delimiter.
Break the section break before executing the trigger, and then perform the creation
Delimiter $
Drop Trigger if exists t_afterinsert_on_tab1$
Create Trigger T_AFTERINSERT_ON_TAB1
After insert on TAB1
For each row
Begin
Insert into TAB2 (tab2_name) values (new.tab1_name);
end$
Insert into TAB1 (tab1_name) VALUES (' Zhang San ') $
This code try to see
------Solution--------------------

SQL Code

Take Eschop's commodity list, with the order form as an example: New commodity table CREATE TABLE goods (ID int auto_increment primary key, #商品id name varchar () NOT NULL Defau  Lt ', #商品名 num tinyint not null default 0 #商品数量) engine MyISAM default charset UTF8; New Order change CREATE TABLE indent (OID int Auto_increment primary KEY, #订单id GID int not NULL for default 0, #商品id much tinyint not null default 0 #购 Buy quantity) engine MyISAM default charset utf8;mysql> desc goods;+-------+-------------+------+-----+---------+---------- ------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+----------------+| ID | Int (11) | NO | PRI | NULL | auto_increment | | name | varchar (30) |     NO |         |                | || num | Tinyint (4) |     NO | |                0 | |+-------+-------------+------+-----+---------+----------------+3 rows in setmysql> desc indent;+-------+------- -----+------+-----+---------+----------------+| Field | Type | Null | Key |Default | Extra |+-------+------------+------+-----+---------+----------------+| OID | Int (11) | NO | PRI | NULL | auto_increment | | GID | Int (11) |     NO | |                0 | || much | Tinyint (4) |     NO | |                0 | |+-------+------------+------+-----+---------+----------------+ Insert demo data: INSERT into goods values (1, ' Samsung phone ', 12), (2, ' ipad computer ', 19 ', (3, ' Motorola MP3 ',;mysql>) * from goods;+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+ manually add purchase records to order table: INSERT into indent (Gid,much) VALUES (3,2);mysql> select * from indent;+-- ---+-----+------+| OID | GID |   Much |+-----+-----+------+|   1 |    3 | 2 |+-----+-----+------+1 row in set to manually reduce product information: Update goods set num=num-2 where id=3;mysql> select * FROM goods;+----+ -------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in set modify MySQL Terminator:mysql> delimiter $---------------------------------------- ---Creating a trigger create TRIGGER Tg1after insert #在插入之后触发on indentfor each row #固定写法beginupdate goods set num=num-1 where id=3;end $------------------------------------------to simulate the user order process: +----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+ order Form: +-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   1 |    3 | 2 |+-----+-----+------+① Order insert into indent (Gid,much) VALUES (2,4) $② View order form +-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   1 |    3 |   2 | |   2 |    2 | 4 |+-----+-----+------+③ Product list should be reduced +----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+ Conclusion: it is obvious that the user placed Order No. 2nd, the next 4 items, order generation! The goods did not decrease, and there was a mistake! -----------------------------------------correct creation of triggers: Create TRIGGER Tg2after Inserton indentfor each rowbeginupdate goods set Num=num-new.much where id=new.gid;end$----------------------------------- An error occurred-----------------------------------------placing the insert trigger on the order: because a table cannot be monitored by 2 triggers at the same time, delete the trigger that started creating mysql> drop tg1$    Query OK, 0 rows affectedmysql> show Triggers$empty set begins to purchase goods (empty order form):mysql> select * from goods; -$+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in setmysql> insert into indent (Gid,much) VALUES (2,4) $Query OK, 1 row affectedmysq L> SELECT * from indent$ #下订单成功 +-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   1 |    2 | 4 |+-----+-----+------+1 row in setmysql> select * from goods$ #对应商品自动减少OK +----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  15 | | 3|  Motorola MP3 | |+----+-------------+-----+3 rows in Set------------------------------------------Cancel order Delete trigger------------------------------------CREATE trigger Tg3after Deleteon indentfor Each rowbeginupdate goods set Num=num+old.much where id=old.gid;end$ Note: In a real project, Never physically delete orders----------------------------------mock cancel order:mysql> select * from goods$+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  15 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in setmysql> select * FROM indent$+-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   1 |    2 | 4 |+-----+-----+------+1 row in setmysql> delete from indent where Oid=1$query OK, 1 row affectedmysql> select * Fro M indent$empty setmysql> SELECT * from goods$+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |  Samsung Phones |  12 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in set------------------------------------Modify order Update trigger-----------------------------------------Modify Order formula: Update goods set num= Num+old.much-new.much where Id=old.gid; key part: New Quantity etc = Number of itself + modified old quantity-newly generated quantity (full mathematical logic), ID invariant create trigger tg4after Updateon Indentfor each rowbeginupdate goods set Num=num+old.much-new.much where id=old.gid;end$-------------------------MySQL > select * from goods$+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |   Samsung Phones |  7 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in setmysql> select * FROM indent$+-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   2 |    1 |   5 |+-----+-----+------+1 row in setmysql> update indent set much=10 where Oid=2$query OK, 1 row affectedrows matched:1 Changed:1 warnings:0mysql> SELECT * from goods$+----+-------------+-----+| ID | name |  Num |+----+-------------+-----+| 1 |   Samsung Phones |  2 | | 2 |  ipad PC |  19 | | 3 |  Motorola MP3 | |+----+-------------+-----+3 rows in setmysql> select * FROM indent$+-----+-----+------+| OID | GID |   Much |+-----+-----+------+|   2 |   1 | |+-----+-----+------+1 row in Set-------------------------------------------trigger base complete! Quick Empty table: truncate [table name] Modify MySQL's Terminator: delimiter $; display trigger: Show triggers Delete trigger: DROP trigger [trigger name] CREATE TRIGGER: Creation Trigger [trigger name]after [trigger behavior/insert/] Update/delete]on [Monitor object/table]for each row #固定写法begin SQL statement; end$ NOTE: A trigger can only correspond to one behavior of a table!  You cannot have multiple triggers to monitor the same behavior of a table!
 