如何编写一个MySQL触发器以将行插入到另一个表中? [英] How to program a MySQL trigger to insert row into another table?

问题描述

我正在寻找在表上创建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:

1. 获取最后插入的行的ID(注释行的ID).
2. 使用最后插入的行中的数据对活动表执行INSERT.

从本质上讲，我将复制此触发器以删除评论.

I'll essentially replicate this trigger for deleting comments.

我遇到的问题:

1. LAST_INSERT_ID()是获取ID的最佳方法吗?
2. 如何正确存储最后插入的注释行中的数据，以便在我的将插入到活动中"语句中使用?
3. 我应该同时使用存储过程和触发器吗?
4. 触发器的基本结构是什么样的?

谢谢！自从我接触到与DB触发器，过程和函数有关的东西已经有几年了.

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;

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)

这篇关于如何编写一个MySQL触发器以将行插入到另一个表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！