Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question

2 Answers 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

//
share|improve this answer
    
I put my new code above, it still got a syntax error. –  omega Apr 29 at 18:51
    
Can you include the exact syntax error including the position it doesn't like? –  atxdba Apr 29 at 18:53
    
ok I put the image of the error above. –  omega Apr 29 at 18:55
    
Try "For each row" before Begin –  atxdba Apr 29 at 18:58
    
If I do that, it gives the error ERROR 1415 (0A000): Not allowed to return a result set from a trigger. –  omega Apr 29 at 19:01

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.