Reputation: 29
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
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
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