SQL After insert trigger insert multiple rows into another table

Question

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.

Abhik Chakraborty · Accepted Answer · 2015-05-14 18:04:51Z

7

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;

edited May 14, 2015 at 18:04

answered May 14, 2015 at 17:26

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marc Rasmussen Over a year ago

Thank you so much mate! :D +1 do you know how i would do whenever i create a user it should add all competences from the competence table where the organization_id is the same?

Abhik Chakraborty Over a year ago

Yes it would be pretty much same. if you provide an example I can write a query for that.

Abhik Chakraborty Over a year ago

Actually when you add an user you also add organization_id and in competence table you have organization_id. So if you insert into user_has_competence it may happen that for same organization id there multiple data so do you want each of them and newly added user_id into the user_has_competence ?

Marc Rasmussen Over a year ago

what i need is basicly: When a new user is added to the database it automaticly adds all competences to competence_has_user and when a new competence has been added then all users in the organization has to get the competence

Abhik Chakraborty Over a year ago

Well that perhaps going to have some logical issue, since when you add any user you do not know anything about competence, all you can get those data from competence having same organization id into the user_has_competence now say you add a new competence and using same trigger you may have duplicate entry. So I would suggest to make it one way. I would suggest to create a new question.

Collectives™ on Stack Overflow

SQL After insert trigger insert multiple rows into another table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related