mysql触发器 修改 插入行_如何编写一个MySQL触发器以将行插入到另一个表中？

小编典典

drop table if exists comments;

create table comments

(

comment_id int unsigned not null auto_increment primary key,

user_id int unsigned not null

)

engine=innodb;

drop table if exists activities;

create table activities

(

activity_id int unsigned not null auto_increment primary key,

comment_id int unsigned not null,

user_id int unsigned not null

)

engine=innodb;

delimiter #

create trigger comments_after_ins_trig after insert on comments

for each row

begin

insert into activities (comment_id, user_id) values (new.comment_id, new.user_id);

end#

delimiter ;

insert into comments (user_id) values (1),(2);

select * from comments;

select * from activities;

编辑：

mysql> \. d:\foo.sql

Database changed

Query OK, 0 rows affected (0.10 sec)

Query OK, 0 rows affected (0.30 sec)

Query OK, 0 rows affected (0.11 sec)

Query OK, 0 rows affected (0.35 sec)

Query OK, 0 rows affected (0.07 sec)

Query OK, 2 rows affected (0.03 sec)

Records: 2 Duplicates: 0 Warnings: 0

+------------+---------+

| comment_id | user_id |

+------------+---------+

| 1 | 1 |

| 2 | 2 |

+------------+---------+

2 rows in set (0.00 sec)

+-------------+------------+---------+

| activity_id | comment_id | user_id |

+-------------+------------+---------+

| 1 | 1 | 1 |

| 2 | 2 | 2 |

+-------------+------------+---------+

2 rows in set (0.00 sec)

2020-05-17