1

I've check many SO threads (one of them here) but couldn't find where the issue lies.

I am trying to protect a column from being updated if it's not null, following this thread.

But I am getting syntax error from mysql. Here's my code:

DELIMITER $$

CREATE TRIGGER lock_x_id
BEFORE UPDATE ON Games
FOR EACH ROW BEGIN
  IF (old.xid IS NOT NULL) THEN
    SIGNAL 'error';
  END IF;
END$$

DELIMITER ;
Community
  • 1
  • 1
Edmund Lee
  • 2,514
  • 20
  • 29

1 Answers1

2

When you try to raise errors via SIGNAL you need to specify the SQLSTATE which is the error code and for the user defined generic error codes its 45000 along with the message text MESSAGE_TEXT

So the trigger becomes as 

delimiter //
create trigger lock_x_id before update on games
for each row
begin
 if old.xid is not null then
   signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';
 end if;
end;//
delimiter ;

Test Case 

mysql> select * from games;
+----+------+------+
| id | xid  | val  |
+----+------+------+
|  1 | NULL |    1 |
|  2 | NULL |    2 |
|  3 | NULL |    3 |
|  4 |    1 |    4 |
|  5 |    2 |    5 |
+----+------+------+

Lets create the trigger now

mysql> delimiter //
mysql> create trigger lock_x_id before update on games
    -> for each row
    -> begin
    ->  if old.xid is not null then
    ->    signal SQLSTATE VALUE '45000' SET MESSAGE_TEXT = 'Your custom error message';
    ->  end if;
    -> end;//
Query OK, 0 rows affected (0.05 sec)


mysql> update games set xid = 4 where id = 1;
Query OK, 1 row affected (0.06 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update games set xid = 5 where id=5;
ERROR 1644 (45000): Your custom error message

And after running the above 2 update commands here how the table looks

mysql> select * from games;
+----+------+------+
| id | xid  | val  |
+----+------+------+
|  1 |    4 |    1 |
|  2 | NULL |    2 |
|  3 | NULL |    3 |
|  4 |    1 |    4 |
|  5 |    2 |    5 |
+----+------+------+

Note that the 2nd update failed and the row is unchanged.

Read more about this https://dev.mysql.com/doc/refman/5.5/en/signal.html

Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • works perfect! now the only thing that bugs me is sequelize dosn't let me set `delimiter`. but thanks so much! – Edmund Lee Mar 16 '17 at 07:13
  • 1
    When you use `sequelize` on node.js , all you need to specify the trigger code and no need to provide `delimiter` the library will take care of that. The delimiter is needed when you run on the CLI. `sequelize.query(/* trigger code*/)` Or you may use hooks to handle the before update event http://docs.sequelizejs.com/en/latest/docs/hooks/ – Abhik Chakraborty Mar 16 '17 at 07:17
  • 1
    oh man, what can I say?! That's it! I can't thank you enough! – Edmund Lee Mar 16 '17 at 07:23