Mysql trigger not working, syntax issues

Question

I have this trigger

CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN 
    SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; 
END;

mysql 5.1.72-0ubuntu0.10.04.1 (Ubuntu)

But I am getting a syntax error, and I don't see where...

EDIT:

DELIMITER //
CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END//
DELIMITER ;

this still got a syntax error

EDIT2:

I think I need the old syntax from here: https://dev.mysql.com/doc/refman/5.0/en/commit.html

I don't know how to complete the code though...

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;

Answer 1

For 5.5 and later it is possible to use signals:

delimiter @

create trigger checkcollision 
after update on players 
for each row 
begin 
    declare dummy int default 0; 
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    if (dummy = 1) then 
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message'; 
    end if; 
end @

delimiter ;

For 5.1 and earlier version there is no support for signal. You could try to mimic it with an forced exception, like division by zero or by referencing something that does not exist. You don't get a nice error message though:

delimiter @

create trigger checkcollision  
after update on players  
for each row  begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        select 1/0 into dummy; 
    end if;
end @

delimiter ;

A slightly more elegant way is to use an exit handler which is supported in 5.1, still no error message though:

create trigger checkcollision  
after update on players  
for each row  
begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    select 1/0 into dummy;
end @

If no wall is found an empty exit handler is invoked, otherwise the trigger continues and an deliberate error is made.

By adding a dummy table like:

create table dummy (msg varchar(100) primary key);

We can force a primary key violation by inserting the same value twice from the trigger:

delimiter @
create trigger checkcollision  
after update on players  
for each row  begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;     
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    insert into dummy (msg) values ('ERROR: Collision')
                                 , ('ERROR: Collision');  
end @
delimiter ;

We will get an error message like (tested in 10.0.20-MariaDB):

ERROR 1062 (23000): Duplicate entry 'ERROR: Collision' for key 'PRIMARY'

You might want to encapsulate this into a stored procedure:

create procedure my_signal (msg varchar(100)) 
begin 
    insert into dummy (msg) 
    values ('ERROR: Collision')
         , ('ERROR: Collision'); 
end @

which can be called from the trigger:

create trigger checkcollision  
after update on players  
for each row  
begin
    declare dummy int default 0; 
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y; 
    call my_signal('ERROR: Collision');  
end @

Answer 2

Since the semicolons need to be part of the trigger definition you need to change your delimiter first. From the prompt first run

delimiter //

Then your create trigger you posted, then finish your statement with just entering your new delimiter in the prompt

//

Answer 3

From here and here I wrote the code below which should work in 5.1 - it inserts an error in a different table - but it could be changed - at least the syntax works.

Here is the trigger

DELIMITER $$
CREATE TRIGGER mytabletriggerexample
BEFORE UPDATE ON billy
FOR EACH ROW BEGIN
  IF(NEW.fred = OLD.fred) THEN
    BEGIN
      DECLARE dummy INT; // if you want to use this 
      SET dummy = 1000;  // value, you can, or a string!
      INSERT INTO errortab VALUES(current_timestamp, NEW.fred, OLD.fred, OLD.mary);
    END;
  END IF; 
END $$
DELIMITER ;

I then ran the code below.

mysql> create table billy( fred int, mary int);
Query OK, 0 rows affected (0.44 sec)

mysql> create table errortab(times timestamp, oldfred int, newfred int, oldmary int)
Query OK, 0 rows affected (0.44 sec)

I then loaded the trigger shown above.

mysql> 
mysql> insert into billy values(1, 2);
Query OK, 1 row affected (0.05 sec)

mysql> insert into billy values(2, 3);
Query OK, 1 row affected (0.14 sec)

mysql> insert into billy values(3, 4);
Query OK, 1 row affected (0.06 sec)

mysql> select * from billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from errortab;
Empty set (0.03 sec)

mysql> 
mysql> update billy set fred = 2 where mary = 3;
Query OK, 0 rows affected (0.09 sec)
Rows matched: 1  Changed: 0  Warnings: 0

*** Note - no rows affected!

mysql> select * from billy
    -> ;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> select * from errortab;
+---------------------+---------+---------+---------+
| times               | oldfred | newfred | oldmary |
+---------------------+---------+---------+---------+
| 2014-07-05 00:58:07 |       2 |       2 |       3 |
+---------------------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> select * from billy;
+------+------+
| fred | mary |
+------+------+
|    1 |    2 |
|    2 |    3 |
|    3 |    4 |
+------+------+
3 rows in set (0.00 sec)

mysql> 

That should be enough to get you started.