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 ;
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
delimiter. but thanks so much!
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 docs.sequelizejs.com/en/latest/docs/hooks