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

I'm looking to create a MySQL trigger on a table. Essentially, I'm creating an activity stream and need to log actions by users. When a user makes a comment, I want a database trigger on that table to fire and:

Grab the ID of the last inserted row (the id of the comment row).

perform an INSERT into an activities table, using data from the last inserted row.

I'll essentially replicate this trigger for deleting comments.

Questions I had:

Is LAST_INSERT_ID() the best way to grab the id?

How do I properly store the data from the last inserted comment row for use in my "INSERT into activities" statement?

Should I be using a combination of stored procedures as well as the trigger?

What would the basic structure of the trigger look like?

Thanks! It's been a few years since I've touched anything to do with DB triggers, procedures and functions.

解决方案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;

Edit:

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)