mysql - Creating a trigger on insert on a table, that creates new rows on another table, according to amount of row on another (3rd) table -

mysql - Creating a trigger on insert on a table, that creates new rows on another table, according to amount of row on another (3rd) table -

assume next structure:

table1:

id | name

table2:

id | name

table3:

id | table1_id | table2_id | value

i want build trigger, after insert table1 if id not exist, create new rows for each row in table2 within table3 corresponding ids.

what did far creating logic in php, have never created triggers complex before don't know how approach this.

example:

customers table after insert: +----+------+ | id | name | +----+------+ | 1 | dan | +----+------+ currency table: +----+------+ | id | name | +----+------+ | 1 | usd | | 2 | eur | +----+------+ customers currency table after trigger +----+---------------+-------------+-------+ | id | customer_id | currency_id | value | +----+---------------+-------------+-------+ | 1 | 1 | 1 | null | | 2 | 1 | 2 | null | +----+---------------+-------------+-------+

to deal need utilize cursor in trigger, here nice tutorial on http://www.mysqltutorial.org/mysql-cursor/

now in case suggest customer table id should primary key auto incremented have unique value

so here how should be

create table client (id int primary key auto_increment , name varchar (100)); create table currency (id int primary key auto_increment, name varchar(100)); insert currency (name) values ('usd'),('eur') ; create table customer_currency (id int primary key auto_increment, customer_id int , currency_id int , val varchar(100));

the trigger as

delimiter // create trigger customer_add after insert on client each row begin declare done int default false; declare currency_id int; declare currency_val varchar(100); declare cur cursor select id,name currency; declare go on handler not found set done = true; open cur; ins_loop: loop fetch cur currency_id,currency_val; if done leave ins_loop; end if; insert customer_currency (customer_id,currency_id,val) values (new.id,currency_id,currency_val); end loop; close cur; end ; // delimiter ;

now in mysql lets add together record on client table

mysql> insert client (name) values ('abhik') ; query ok, 1 row affected (0.02 sec)

now lets see there in customer_currency symbol

mysql> select * customer_currency ; +----+-------------+-------------+------+ | id | customer_id | currency_id | val | +----+-------------+-------------+------+ | 1 | 1 | 1 | usd | | 2 | 1 | 2 | eur | +----+-------------+-------------+------+

in trigger adding currency value in 3rd table if not want can ignore , become null.

you can write after delete trigger on client , delete info customer_currency customer_id id of deleted row in client table.

mysql sql triggers