How to program a MySQL trigger to insert row into another table?

Question

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. Grab the ID of the last inserted row (the id of the comment row).
2. 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:

1. Is LAST_INSERT_ID() the best way to grab the id?
2. How do I properly store the data from the last inserted comment row for use in my "INSERT into activities" statement?
3. Should I be using a combination of stored procedures as well as the trigger?
4. 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.

Agronomy

Answer 1

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)

Diphenyl

For all wondering what the "new" in here is standing for: You can refer to columns in the subject table (the table associated with the trigger) by using the aliases OLD and NEW. OLD.col_name refers to a column of an existing row before it is updated or deleted. NEW.col_name refers to the column of a new row to be inserted or an existing row after it is updated. dev.mysql.com/doc/refman/5.0/en///create-trigger.html – Mammy

@f00, can you please tell me in this trigger ... as there is a clause for each row will it scan whole table, or will insert only new rows. Thanks for your help – Gaughan

@SAM, "for each row" considers only the updated/inserted rows according to mysql documentation. – Streamy

Why i am getting this error ? #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into comments (user_id) values (1),(2)' at line 7 on mysql phpmyadmin – Jairia

@Jairia if I remember correctly phpmyadmin doesn't like the delimiter ; --script runs fine in mysql console. – Diphenyl

not working when i try to access column name using . – Bandanna