CODEWITHSUNDEEP
mysqlsql-servertriggers
Marc Rasmussen
Marc Rasmussen

Reputation: 20555

SQL After insert trigger insert multiple rows into another table

So i have the following table:

Table: competence
Columns:
id  int(11) AI PK
name    varchar(400)
organization_id int(11)
competence_type_id  int(11)
competence_category_id  int(11)

And the following user table:

    Table: user
Columns:
id  int(11) AI PK
username    varchar(100)
password    varchar(100)
is_active   int(11)
user_type_id    int(11)
token   varchar(445)
organization_id int(11)
title_id    int(11)
image_path  varchar(100)
division_id int(11)

And the following connection between these two tables:

Table: user_has_competence
Columns:
user_id int(11) PK
competence_id   int(11) PK
competence_level_id int(11)
progression varchar(45)
id  int(11) AI PK

Now im trying to create a trigger on the competence table that does the following:

After a row has been inserted into the competence table find all users with the same organization_id and then for each of these users insert into user_has_competence with the user_id and competence_id.

However i havnt worked with triggers that much and was hoping one of you guys could push me in the right direction.

Upvotes: 1

Views: 3841

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

In mysql it could be done as

delimiter //
create trigger competence_ins after insert on competence
for each row 
begin
 insert into user_has_competence (user_id,competence_id)
 select u.id,c.id from competence c
 join user u on u.organization_id = c.organization_id and c.organization_id = new.organization_id;
end;//

delimiter;

Upvotes: 7

Related Questions