CODEWITHSUNDEEP
mysqlsqltriggers
JerryH
JerryH

Reputation: 29

Make trigger update the rows I want

I am struggling with a trigger to update a second table based on the first table being updated.

I have tried this:

DELIMITER //
FOR EACH ROW
BEGIN
Update phpfb_picks
set points = NEW.value
where username = user and gameid = gameid
END;
//
delimiter;

but get an error about syntax.

If I do:

Update phpfb_picks
set points = NEW.value
where username = user and gameid = gameid

This works to a point. It updates all users records with the same value that was updated.

What I want to do is, when a value is updated on Table A, I want to update Table B with all records for that user, basically the 'value' from table A would always be pushed to 'points' of table B for the user, regardless if the actual record was updated.

If any record for user is updated, update all of table B records with the same value based on the username and gameid

So if Table A has the following records: username - test gameid - 1 value - 1

username - test gameid - 2 value - 2

when value from row 1 is updated to 3, i want to update Table B with all the current values from table A for that user.

Is this even possible?

UPDATE:

Table A (allpoints) has columns:
username
gameid
value

TABLE B (phpfb_picks) has columns:
user
gameid
points

allpoints.username = phpfb_picks.user
allpoints.gameid = phpfb_picks.gameid
allpoints.value = phpfb_picks.points

Whenever an update is made to allpoints, I want all the records for that specific user to update all the specific records for that user in phpfb_picks, passing the allpoints.value to phpfb_picks.points based on the user and gameid

Upvotes: 0

Views: 71

Answers (2)

P.Salmon
P.Salmon

Reputation: 17665

Maybe this

drop trigger if exists phpfb;
DELIMITER $$
create trigger phpfb after update on allpoints
FOR EACH ROW
BEGIN
Update phpfb_picks
set points = NEW.value
where user = new.username  and gameid = new.gameid;
END $$
delimiter ;

for example

drop table if exists allpoints,phpfb_picks;

create table allpoints (username char(4),gameid int, value int);
create table phpfb_picks (user char(4),gameid int, points int);
insert into allpoints (username,gameid) values
('test',1),('test',2);

insert into phpfb_picks (user,gameid) values
('test',1),('test',1),('test',2);

update allpoints
    set value = 2 where username = 'test' and gameid = 1;

result

+------+--------+--------+
| user | gameid | points |
+------+--------+--------+
| test |      1 |      2 |
| test |      1 |      2 |
| test |      2 |      0 |
+------+--------+--------+
3 rows in set (0.00 sec)

Upvotes: 0

stjernaluiht
stjernaluiht

Reputation: 750

Try this trigger: 

DELIMITER $$
CREATE TRIGGER update_phpfb_picks 
    AFTER UPDATE ON allpoints FOR EACH ROW 
    BEGIN
        UPDATE phpfb_picks
        INNER JOIN allpoints ON allpoints.username = phpfb_picks.username AND
                                allpoints.gameid = phpfb_picks.gameid
        SET phpfb_picks.points = allpoints.value
        WHERE phpfb_picks.username = NEW.username;
    END;
$$
DELIMITER ;

For each row that is updated in allpoints, all rows in phpfb_picks with the same username of the updated row will be updated to the respective value of points present in allpoints as value. The trigger is an AFTER UPDATE so that the newly updated value in allpoints will also be set in phpfb_picks.

Upvotes: 1

Related Questions