MYSQL Workbench. Trigger that blocks a certain insertion

Question

I'm desperately trying to make the database replace the 0 with 1 every time I insert it in a table field. I have this field called Stazione, in which there is "numerodibinari", and I don't want it to be zero. I've tried like this.

Create trigger gestionebinari

Before insert on stazione

for each row

Begin
If (new.'numero di binari'=0) 

then
Set (new.'numero di binari'=1);

but it still doesn't work. What can I do?

It keeps saying me that my syntax is wrong. Numero di binari is a column name. — Pierluigi Rizzu, Dec 07 '17 at 16:00
Please check: [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) — Álvaro González, Dec 07 '17 at 16:35

score 1 · Accepted Answer · edited Jun 13 '18 at 17:12

1

You cannot use single quotes to escape column names. Use back ticks (`), instead. BTW white spaces in column names is not a good idea, nor is random unnecessary bracketing. Try

drop table if exists stazione;
create table stazione(`numero di binari` int);

drop trigger if exists gestionebinari;

delimiter $$
Create trigger gestionebinari
Before insert on stazione
for each row
Begin
If new.`numero di binari`=0 
then
Set new.`numero di binari`=1;
end if;
end $$
delimiter ;

insert into stazione values (0);
select * from stazione;

mysql> select * from stazione;
+------------------+
| numero di binari |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

edited Jun 13 '18 at 17:12

JonathanDavidArndt

2,518
13
37
49

answered Dec 07 '17 at 16:04

P.Salmon

17,104
2
12
19

It perfectly works. Thank you so much. Would you mind explain to me how does the delimiter thing and hte end$$ works? – Pierluigi Rizzu Dec 07 '17 at 16:09
Please read https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html – P.Salmon Dec 07 '17 at 16:14
Thank you so much for your help! – Pierluigi Rizzu Dec 07 '17 at 16:23
@PierluigiRizzu You could accept this answer as solution https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work – Raymond Nijland Dec 07 '17 at 16:40

MYSQL Workbench. Trigger that blocks a certain insertion

1 Answers1