MySQL trigger - Update table2 on insert in table1

Question

What I want to do is to add some credit (10% of user´s credit) to user´s account with each 3rd, 6th, 9th and 12th (not more) user added into database under this user´s invite link (but invite link isn´t essential here - each user has index column with ID of his invitator).

So something like this (sorry for "pseudocode", I never had to use the trigger and probably won´t have to any soon, so I have no idea how to write it properly):

UPDATE accounts.credit = accounts.credit + (accounts.credit/10) ON INSERT INTO users (AND when inserted row % 3 == 0 to some user) WHERE k_user = this

Or is there any easier way how to do this? I could handle PHP, but I think the script can execute only if user visits the site...

Alexey · Accepted Answer · 2017-04-19 06:19:04Z

1

Consider using triggers https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

delimiter $$ CREATE TRIGGER upd_check AFTER INSERT ON users FOR EACH ROW BEGIN IF NEW.id % 3 = 0 THEN UPDATE accounts SET credit = credit + (credit / 10) where k_user = NEW.id END IF; END;$$ delimiter ;

edited Apr 19, 2017 at 6:19

answered Apr 18, 2017 at 19:13

Alexey

2,4881 gold badge21 silver badges34 bronze badges

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

1 Comment

Michal S Over a year ago

Thank you for the answer! In the end I realised I don´t need necessarily do this in the database level and solved it via PHP. Just added some sql queries at the end of the function that handles the user registration. But your answer gave me some insight in the triggers...which could be handy.

Collectives™ on Stack Overflow

MySQL trigger - Update table2 on insert in table1

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related