if statement - '' Single quote syntax error in mysql but no single quotes in query -

if statement - '' Single quote syntax error in mysql but no single quotes in query -

the next query:

create trigger `update_staff_contract_contract_document_last_changed` before update on `staff_contract` each row begin if (new.contract_document != old.contract_document) set new.contract_document_last_changed = utc_timestamp(); end if; end;

fails error you have error in sql syntax; check manual corresponds mysql server version right syntax utilize near '' @ line 4.

however there's no single quotes in query. if remove begin/end , if/else syntax works, e.g.

create trigger `update_staff_contract_contract_document_last_changed` before update on `staff_contract` each row set new.contract_document_last_changed = utc_timestamp();

it must begin/end or if/else, don't know what, idea?

quote dev.mysql.com

by using begin ... end construct, can define trigger executes multiple statements. within begin block, can utilize other syntax permitted within stored routines such conditionals , loops. however, stored routines, if utilize mysql programme define trigger executes multiple statements, necessary redefine mysql statement delimiter can utilize ; statement delimiter within trigger definition. next illustration illustrates these points. defines update trigger checks new value used updating each row, , modifies value within range 0 100. must before trigger because value must checked before used update row

the problem using multiple statements , should separate them statement delimiter ; character default.

edit details:

the default delimiter , delimiter used in trigger's body same , mysql thinks create trigger statement ends @ first ; character. in case means there syntax error (since begin..end block , trigger not finished).

the '' in error message means, there empty statement, not single or double quotation mark.

you have alter delimiter create trigger:

delimiter // create trigger `update_staff_contract_contract_document_last_changed` before update on `staff_contract` each row begin if (new.contract_document != old.contract_document) set new.contract_document_last_changed = utc_timestamp(); end if; end// delimiter ;

mysql if-statement