Mysql - Create trigger to insert a new row based on a row inserted in another table

Question

Hi I have two tables called users and userrewards. When a new row gets inserted into the users table, I want to take the userid and create a new row in the userrewards table, only if the usertype is "premium". In the userrewards table I just need to insert the userid - the rest of the fields are set to default values until the user makes changes themselves. How can I do this via a trigger?

check dev.mysql.com/doc/refman/5.7/en/create-trigger.html

realbart
– realbart

2017-09-11 16:24:20 +00:00
Commented Sep 11, 2017 at 16:24 — realbart
– realbart, Commented Sep 11, 2017 at 16:24

realbart · Accepted Answer · 2017-09-11 16:35:40Z

3

You need to create an "after instert" trigger on user: that's the table you're watching. Now, if I guessed your column names correctly, the sql for creating the trigger should look like this:

CREATE TRIGGER user_ai AFTER INSERT ON user 
FOR EACH ROW
BEGIN
    IF new.usertype = 'premium' THEN
        INSERT INTO userrewards (user_id) VALUES new.id;
    END IF;
END;

Another example of a trigger with if statements and :old / :new values can be found here. MySQL documentatation for create trigger here. MySQL documentation for the if-syntax here.

edited Sep 11, 2017 at 16:35

answered Sep 11, 2017 at 16:26

realbart

4,1322 gold badges34 silver badges43 bronze badges

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

2 Comments

Bill Karwin Over a year ago

MySQL does not use the colon before NEW and OLD. I think that's Oracle syntax.

realbart Over a year ago

Thanks, @BillKarwin. You're right: I confused MySQL with Oracle.

Collectives™ on Stack Overflow

Mysql - Create trigger to insert a new row based on a row inserted in another table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related