mesumosu mesumosu - 2 months ago 16
MySQL Question

Trigger update the same table with data from another table after insert

I have two unrelated tables tbl_A & tbl_B

tbl_A

+----+---------------------+------+
| id | url | slug |
+----+---------------------+------+
| 1 | http://example.com/ | 3qqd |
| 2 | http://example.com/ | t8af |
| 3 | http://example.com/ | sjim |
| 4 | http://example.com/ | awfo |
| 5 | http://example.com/ | 6myy |
+----+---------------------+------+


tbl_A description:

+---------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------------------+----------------+
| id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| url | text | YES | | NULL | |
| slug | varchar(255) | YES | MUL | NULL | |
+---------------------+---------------------+------+-----+---------------------+----------------+


and another table :

tbl_B

+----+---------------------+---------------------+------+
| ID | user_name | url | slug |
+----+---------------------+---------------------+------+
| 1 | john.reese | NULL | NULL |
+----+---------------------+---------------------+------+


tbl_B description :

+---------------------+---------------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------------+------+-----+---------------------+----------------+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| user_name | varchar(60) | NO | MUL | | |
| url | text | YES | | NULL | |
| slug | varchar(255) | YES | MUL | NULL | |
+---------------------+---------------------+------+-----+---------------------+----------------+


tbl_A.id is unrelated to tbl_B.ID.

tbl_B.ID is userID field and gets filled up dnamically when a new user registers. So that tbl_B.ID gets a row inserted automatically as a user register, tbl_B.ID value gets auto incremented.

tbl_A on the other hand already exists with all the details.


What I want to achieve: whenever a new user registers and userID is
INSERT into tbl_B.ID, at the same time it should trigger an update of
tbl_B.user and tbl_B.slug with the values taken from tbl_A.user and
tbl_A.slug.


Outcome: After ID 1 is added

+----+---------------------+---------------------+------+
| ID | user_name | url | slug |
+----+---------------------+---------------------+------+
| 1 | john.reese | http://example.com/ | 3qqd |
+----+---------------------+---------------------+------+


Hope I am able to explain. I was trying to use triggers but got lost, am a newbie with mysql, please bear with me.

drop trigger if exists bi_tbl_B $$
delimiter $$

create trigger bi_tbl_B before insert on tbl_B
for each row begin
UPDATE tbl_B
SET url = url +
(SELECT url
FROM tbl_A
WHERE id = NEW.id)
WHERE ID = NEW.ID;

end;
$$

delimiter ;


I don't know whether this is even possible or should I try the other way round.

Add a field
user_id
in tbl_A and
AFTER INSERT
on tbl_B update tabl_A.user_id column with the userID from tbl_B.ID

I am open to suggestions, if not trigger then procedures.

Answer

It is possible to do it, just not the way you are trying. In the before insert trigger you can change the values being inserted by changing the NEW.field_name variables.

drop trigger if exists bi_tbl_B $$
delimiter $$

create trigger bi_tbl_B before insert on tbl_B
for each row begin
   DECLARE v_slug as varchar(255);
   DECLARE v_url as text;
   SELECT url, slug INTO v_url, v_slug FROM tbl_A WHERE id = NEW.id;
   NEW.url=v_url;
   NEW.slug=v_slug;
end;
$$

delimiter ;