title: How to rollback update in mysql after update trigger?
body: <p>I am using mysql 5.1.72-0ubuntu0.10.04.1 (Ubuntu). And I want to create a trigger that stops collisions between players and walls.</p>
  
  <p>The players and walls table both have an x and y column.</p>
  
  <p>I have this trigger:</p>
  
  <pre><code>CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN 
      SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; 
  END;
  </code></pre>
  
  <p>I want to know if this trigger will roll back the update if there is any rows that are actually selected, given my mysql version. Or do I have to manually do the rollback?</p>
  
  <p>Also, there is a syntax problem with my trigger, and I'm not sure what it is..</p>
  
  <p>Does anyone know?</p>
  
  <p>EDIT:</p>
  
  <p>I think I need the old syntax from here:
  <a href="https://dev.mysql.com/doc/refman/5.0/en/commit.html" rel="nofollow">https://dev.mysql.com/doc/refman/5.0/en/commit.html</a></p>
  
  <p>I don't know how to complete the code though...</p>
  
  <pre><code>START TRANSACTION;
  SELECT p.* FROM players p WHERE EXISTS (SELECT w.* FROM walls w WHERE w.x=p.x AND w.y=p.y);
  COMMIT;
  ROLLBACK;
  </code></pre>
  
  <p>EDIT2:</p>
  
  <p>This is getting a syntax error:</p>
  
  <pre><code>DELIMITER // 
  CREATE TRIGGER checkcollision AFTER UPDATE ON players 
      FOR EACH ROW 
      BEGIN 
          IF (SELECT count(*) FROM walls WHERE NEW.x=x AND NEW.y=y)&gt;0 THEN 
              SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Collision detected'; 
          END IF; 
      END;// 
  DELIMITER ; 
  </code></pre>