How to create a trigger between two tables

How do I create a trigger between two tables? Student table: student ID, name, age, department number, department name table: Department number, department name, name, age. How to add a record to the student table is also automatically added to the table. Similarly, how does one delete a file ?, Of course! How do you create a trigger between two tables in http://www.cnblogs.com/nicholas_f/archive/2 like this blog post?
Student table: student ID, name, age, Department ID, department name
Department table: Department number, department name, name, and age.

How to add a record to the student table is also automatically added to the table. Similarly, how does one delete a file?

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

Hope you can share your results after success
------ Solution --------------------
I have never done it, so I want you to look at the examples
If you want to read the manual, isn't it 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 column can be used for reference!
------ Solution --------------------
The syntax is incorrect. Paste your SQL string.
------ Solution --------------------
The statement is correct. it should be a separator.
Before executing the trigger, modify the subscriber and create the subscriber.
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') $
Try this code.
------ Solution --------------------

SQL code

Take the eschop product table and the order table as an example: create table goods (id int auto_increment primary key, # Item id name varchar (30) not null default '', # commodity name num tinyint not null default 0 # commodity quantity) engine myisam default charset utf8; create order change create table indent (oid int auto_increment primary key, # Order id gid int not null default 0, # Item id much tinyint not null default 0 # quantity purchased) 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 PC', 19), (3, 'Motorola MP3', 3 8); mysql> select * from goods; + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad computer | 19 | 3 | motorola mp3 | 38 | + ---- + ------------- + ----- + add purchase records to the order table manually: insert into indent (gid, much) values (3, 2); mysql> select * from indent; + ----- + ------ + | oid | gid | much | + ----- + ------ + | 1 | 3 | 2 | + ----- + ------ + 1 row in set manually reduce commodity information for the commodity table: update goods set n Um = num-2 where id = 3; mysql> select * from goods; + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad computer | 19 | 3 | motorola mp3 | 36 | + ---- + ------------- + ----- + 3 rows in set modify the mysql Terminator: mysql> delimiter $ ----------------------------------------- create trigger tg1after insert # trigger on indentfor each row after insert # beginupdate goods set num = num-1 where id = 3; End $ ------------------------------------------ the following table describes the products used to simulate the order placement process: + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad computer | 19 | 3 | motorola mp3 | 36 | + ---- + ------------- + ----- + order table: + ----- + ------ + | oid | gid | much | + ----- + ------ + | 1 | 3 | 2 | + ----- + ------ + ① place an order insert into indent (gid, much) values (2, 4) $ ② view order table + ----- + ------ + | oid | gid | much | + ----- + ------ + | 1 | 3 | 2 | 2 | 2 | 4 | + ----- + ------ + ③ the commodity table should be reduced by + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad | 19 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + conclusion: apparently, the user placed order 2, 4 items, and the order was generated! The product has not been reduced, and an error has occurred! --------------------------------------- Create trigger correctly: create trigger tg2after inserton indentfor each rowbeginupdate goods set num = num-new.much where id = new. gid; end $ ----------------------------------- an error occurred while placing the insert trigger orders: Because one table cannot be monitored by two triggers at the same time, you need to delete the trigger mysql> drop tg1 $ Query OK, 0 rows affectedmysql> show triggers $ Empty set to start purchasing items (clear order table): mysql> select * from goods;-> $ + ---- + ------------- + ----- + | Id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad computer | 19 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + 3 rows in setmysql> insert into indent (gid, much) values (2, 4) $ Query OK, 1 row affectedmysql> select * from indent $ # Order placed successfully + ----- + ------ + | oid | gid | much | + ----- + ------ + | 1 | 2 | 4 | + ----- + ------ + 1 row in setmysql> select * from g Oods $ # The corresponding product automatically reduces OK + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad | 15 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + 3 rows in set orders cancel order delete trigger cancel create trigger tg3after deleteon indentfor each rowbeginupdate goods set num = num + old. much where id = old. gid; end $ note: real projects will never be physically deleted Order ------------------------------------ simulated order cancellation: mysql> select * from goods $ + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad computer | 15 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + 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 * from indent $ Empty setmysql> select * from goods $ + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 12 | 2 | ipad | 19 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + 3 rows in set ---------------------------------- modify the order update trigger quota order formula: update goods set num = num + old. much-new.much Where id = old. gid; key part: New quantity equal to itself quantity + modified old quantity-New quantity (full mathematical logic ), id unchanged 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 mobile phone | 7 | 2 | ipad | 19 | 3 | Motorola mp3 | 35 | + ---- + ------------- + ----- + 3 rows in setmys Ql> 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: 0 mysql> select * from goods $ + ---- + ------------- + ----- + | id | name | num | + ---- + ------------- + ----- + | 1 | Samsung mobile phone | 2 | 2 | ipad | 19 | 3 | Motorola mp3 | 35 | + ---- --------- + ----- + 3 rows in setmysql> select * from indent $ + ----- + ------ + | oid | gid | much | + ----- + ------ + | 2 | 1 | 10 | + ----- + ------ + 1 row in set --------------------------------------------- trigger basics complete! Quick table clearing: truncate [table name] modify mysql Terminator: delimiter $; display trigger: show triggers delete trigger: drop trigger [trigger name] CREATE trigger: create trigger [trigger name] after [trigger behavior/insert/update/delete] on [monitoring object/A table] for each row # fixed syntax begin SQL statement; end $ note: A trigger can only correspond to one action of a table! You cannot use multiple triggers to monitor the same behavior of a table!