Mysql trigger not working, syntax issues

MySQLtrigger

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

enter image description here

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;

Best Answer

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 @
Related Question