0

I have such SqLite trigger:

CREATE TRIGGER update_rating AFTER UPDATE ON gameServers 
FOR EACH ROW BEGIN UPDATE gameServers 
SET rated_order=NEW.rating || '#' || NEW._address 
WHERE rowid=NEW.rowid; END;

Help me, please, with converting it into MySQL.

Max Frai
  • 61,946
  • 78
  • 197
  • 306
  • 1
    On the side, just watch out: http://stackoverflow.com/questions/2334478/mysql-triggers-cannot-update-rows-in-same-table-the-trigger-is-assigned-to-sugg (not possible to manipulate rows other than the one the trigger was called for in mysql triggers). Not your problem (right now), but maybe later... and there are good examples there. – Mörre Feb 17 '12 at 11:31

1 Answers1

1
CREATE TRIGGER update_rating BEFORE UPDATE ON gameServers
FOR EACH ROW SET NEW.rated_order=CONCAT(NEW.rating,' # ',NEW.address);

OF COURSE - this does nothing ON INSERT...(!)

Note that I changed it from AFTER to BEFORE (quite deliberately): Apart from the question why I should start another UPDATE after the one triggering the trigger, there is the issue Updating table in trigger after update on the same table Your (full) "UPDATE" statement in your AFTER trigger would cause a circular trigger-calling-trigger-calling-trigger... (which mysql would prevent by refusing the statement)

EDIT: At first I wanted to use the '||' too for string concatenation, but this is MySQL and not Oracle :)

It should work, I actually tested it on one of my own tables just to be sure. This trigger stuff is fickle at times :)

Community
  • 1
  • 1
Mörre
  • 5,699
  • 6
  • 38
  • 63
  • It will work, because it will help to change the record before modifying. – Devart Feb 17 '12 at 13:20
  • @Mörre You are correct about concatenation operator ||, but you can configure mysql server to accept it in form || like oracle do.Just to note :) – rkosegi Feb 17 '12 at 19:08
  • Cool, thanks, didn't know that. Won't use it though, with software and hardware I always try to use the "defaults" - so no widgets or fancy stuff on my screen and no "decorations" in or on my car :) I want to know what everybody else gets (and since I'm a freelancer, no feelings of loss when I go to the next client). – Mörre Feb 18 '12 at 11:39