How to write a trigger to insert and update in MySql

Question

I have two tables users and media.

When i insert a new user, i want to create a trigger to automatically insert a new media row, get the new inserted media.id to update the users.media_id

how can this be done? appreciate the help

EDIT:

here is the media table definition. media_id is the primary key with the auto increment.

CREATE TABLE `media` (
  `media_id` int(10) UNSIGNED NOT NULL,
  `media_image` tinytext,
  `media_create_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `media_update_date` datetime NOT NULL
)

users table:

CREATE TABLE `tf_user` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_name` varchar(50) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_gender` varchar(1) NOT NULL, 
  `user_media_id` int(10) UNSIGNED
)

post also users definition. and logic behind what to update/populate in users — amdixon, Dec 07 '15 at 13:25
the logic is automatically generating a media_id for users upon insert. even if the media_image is null at start. (i cannot change the structure, have to work on it as is) — TareK Khoury, Dec 07 '15 at 13:34
See http://stackoverflow.com/questions/16892070/mysql-after-insert-trigger-which-updates-another-tables-column/ for a similar example — Richard St-Cyr, Dec 07 '15 at 13:57

score 1 · Answer 1 · answered Dec 07 '15 at 14:21

setup

CREATE TABLE `media` (
  `media_id` int(10) UNSIGNED NOT NULL,
  `media_image` tinytext,
  `media_create_date` datetime NOT NULL,
  `media_update_date` datetime NOT NULL
);

CREATE TABLE `tf_user` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `user_name` varchar(50) NOT NULL,
  `user_email` varchar(100) NOT NULL,
  `user_gender` varchar(1) NOT NULL, 
  `user_media_id` int(10) UNSIGNED
);

trigger definition

CREATE TABLE `seq` (
  `val` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`val`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

insert into seq(val) values(1);

delimiter $$
create function seq() returns int
begin
 update seq set val=last_insert_id(val+1);
 return last_insert_id();
end $$

create trigger tr_ins_user before insert
on tf_user for each row
begin
declare media_id int(10);

select seq() into media_id;

insert into media
( media_id, media_image, media_create_date, media_update_date )
select media_id, null as media_image, 
current_date as media_create_date, null as media_update_date
;

set NEW.user_media_id = media_id;

end $$
delimiter ;

IO

mysql> insert into tf_user(user_id, user_name, user_email, user_gender, user_media_id) values(3, 'test', 'test@test.com', 'M', -1);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> select *
    -> from tf_user
    -> ;
+---------+-----------+---------------+-------------+---------------+
| user_id | user_name | user_email    | user_gender | user_media_id |
+---------+-----------+---------------+-------------+---------------+
|       3 | test      | test@test.com | M           |             2 |
+---------+-----------+---------------+-------------+---------------+
1 row in set (0.00 sec)

mysql> select *
    -> from media
    -> ;
+----------+-------------+---------------------+---------------------+
| media_id | media_image | media_create_date   | media_update_date   |
+----------+-------------+---------------------+---------------------+
|        2 | NULL        | 2015-12-08 00:00:00 | 0000-00-00 00:00:00 |
+----------+-------------+---------------------+---------------------+
1 row in set (0.00 sec)

How to write a trigger to insert and update in MySql

1 Answers1