I have two tables, People
and Shifts
. I want to write a trigger that takes entries in Shifts
and updates the values in People
by adding a value from the Shifts.Shift
column to the Coordinates
column in People
. However, my code seems to affect every entry under People
the same way. I'll illustrate below.
CREATE TABLE People(
`id` INT,
`Name` VARCHAR(10),
`Coordinates` INT);
CREATE TABLE Shifts(
`id` INT,
`Name` VARCHAR(10),
`Shift` INT);
INSERT INTO People VALUES (1,"Ashley",28),(2,"Bob",101),(3,"Curtis",31),(4,"Daniel",69),(5,"Esther",3);
CREATE TRIGGER Shifting_Location
AFTER INSERT ON Shifts
FOR EACH ROW
UPDATE People
SET `id` = NEW.`id`,
`Name` = NEW.`Name`,
`Coordinates` = `Coordinates` NEW.`Shift`;
Here are the tables:
People
id | Name | Coordinates |
---|---|---|
1 | Ashley | 28 |
2 | Bob | 101 |
3 | Curtis | 31 |
4 | Daniel | 69 |
5 | Esther | 3 |
Shifts (empty at first)
id | Name | Shift |
---|---|---|
Now if I insert a record into Shifts
like so:
INSERT INTO Shifts VALUES (1, "Ashley", 12)
I want to get
id | Name | Coordinates |
---|---|---|
1 | Ashley | 40 |
2 | Bob | 101 |
3 | Curtis | 31 |
4 | Daniel | 69 |
5 | Esther | 3 |
id | Name | Shift |
---|---|---|
1 | Ashley | 12 |
Where the 28 next to Ashley had 12 added to it and is now 40.
However, what I get instead is:
id | Name | Coordinates |
---|---|---|
1 | Ashley | 40 |
1 | Ashley | 113 |
1 | Ashley | 43 |
1 | Ashley | 81 |
1 | Ashley | 15 |
The (1, "Ashley")
has overwritten all the other record entries, and all the values in coordinates had 12 added to them instead of just the first row. Again, here is the trigger I wrote to achieve this:
CREATE TRIGGER Shifting_Location
AFTER INSERT ON Shifts
FOR EACH ROW
UPDATE People
SET `id` = NEW.`id`,
`Name` = NEW.`Name`,
`Coordinates` = `Coordinates` NEW.`Shift`;
CodePudding user response:
CREATE TRIGGER Shifting_Location
AFTER INSERT ON Shifts
FOR EACH ROW
UPDATE People
SET Coordinates = Coordinates NEW.Shift
WHERE id = New.id;