如何编写 MySQL 触发器以将行插入另一个表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4753878/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to program a MySQL trigger to insert row into another table?
提问by Eric
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:
我正在寻找在表上创建一个 MySQL 触发器。本质上，我正在创建一个活动流并且需要记录用户的操作。当用户发表评论时，我希望该表上的数据库触发器触发，并且：
- 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.
- 获取最后插入行的 ID（评论行的 id）。
- 使用最后插入的行中的数据对活动表执行 INSERT。
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?
- LAST_INSERT_ID() 是获取 id 的最佳方式吗？
- 如何正确存储最后插入的注释行中的数据以用于我的“插入到活动”语句中？
- 我应该使用存储过程和触发器的组合吗？
- 触发器的基本结构是什么样的？
Thanks! It's been a few years since I've touched anything to do with DB triggers, procedures and functions.
谢谢！我已经有几年没有接触过与 DB 触发器、过程和函数有关的任何事情了。
回答by Jon Black
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)
