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;