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?

Answer 1

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.