I'm trying to set up a MySQL trigger, but I can't figure out how exactly to get what I want done. I think I need to set up a MySQL @variable but what I've tried hasn't worked and I have not been able to find a good resource figure it out. I change the delimiter in phpMyAdmin, and the comment in the below is not used in the actual query.
CREATE TRIGGER coroner AFTER INSERT ON events
FOR EACH ROW BEGIN
UPDATE teams SET live = live-1 WHERE id = NEW.victim;
UPDATE teams SET score = score+NEW.value WHERE id = NEW.shooter;
UPDATE teams SET last_active = NEW.time WHERE id = NEW.shooter OR id = NEW.victim;
// up to here all works fine
IF (SELECT live FROM teams WHERE id = NEW.victim) = 0 THEN
UPDATE teams SET slot = slot-1 WHERE slot > OLD.slot;
ENDIF;
END
What I'm actually doing here is taking a team arranged in a circle, and when the last player of the team has been eliminated, I'm closing the circle up without it.
Here is the error message from phpMyAdmin:
1363 - There is no OLD row in on INSERT trigger
thanks for any help
new attempt:
CREATE TRIGGER coroner AFTER INSERT ON events
FOR EACH ROW BEGIN
UPDATE teams SET live = live-1 WHERE id = NEW.victim;
UPDATE teams SET score = score+NEW.value WHERE id = NEW.shooter;
UPDATE teams SET last_active = NEW.time WHERE id = NEW.shooter OR id = NEW.victim;
SET @pivot := (SELECT slot FROM teams WHERE id = NEW.victim);
IF (SELECT live FROM teams WHERE id = NEW.victim) = 0 THEN
UPDATE teams SET slot = slot+1 WHERE slot < @pivot;
ENDIF;
END
error:
1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '; END' at line 9