CODEWITHSUNDEEP
mysqlsqltriggerssql-insert
Michal S
Michal S

Reputation: 1113

MySQL trigger - Update table2 on insert in table1

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...

Upvotes: 0

Views: 96

Answers (1)

Alexey
Alexey

Reputation: 2488

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 ;

Upvotes: 1

Related Questions