Почему выбрасывает ошибку mysql?

Question

[easy_katka]

CREATE TRIGGER marketing_list_update
AFTER UPDATE
ON cms_offers
FOR EACH ROW
begin
if NEW.status = 'active' 
then UPDATE marketing_credits_requests SET sum_s=NEW.ammount, statut=NEW.status WHERE id_req=old.id;
else then UPDATE marketing_credits_requests SET  statut=NEW.status WHERE id_req=old.id;
end;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 7

Делал все по докам mysql, версия 5.5.25

Answer 1

[galaxy]

Синтаксис блока IF такой:

IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

У вас после else идет then и нет end if.

Comments

[easy_katka]

CREATE TRIGGER marketing_list_update
AFTER UPDATE
ON cms_offers
FOR EACH ROW
IF NEW.status = 'active' then UPDATE marketing_credits_requests SET sum_a=NEW.ammount, statut=NEW.status WHERE id_req=old.id;
else UPDATE marketing_credits_requests SET  statut=NEW.status WHERE id_req=old.id;
END IF;

та же ошибка
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

[galaxy]

BEGIN/END зачем убрали?

[easy_katka]

galaxy,

CREATE TRIGGER marketing_list_update
AFTER UPDATE
ON cms_offers
FOR EACH ROW
begin
IF NEW.status = 'active' then UPDATE marketing_credits_requests SET sum_a=NEW.ammount, statut=NEW.status WHERE id_req=old.id;
else UPDATE marketing_credits_requests SET  statut=NEW.status WHERE id_req=old.id;
END IF;
end;

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 6

[galaxy]

А да, перед созданием триггера поменяйте delimiter, например:
delimiter |
иначе он на ; внутри триггера реагирует.

> delimiter |
> CREATE TRIGGER marketing_list_update
AFTER UPDATE
ON cms_offers
FOR EACH ROW
begin
IF NEW.status = 'active' then UPDATE marketing_credits_requests SET sum_a=NEW.ammount, statut=NEW.status WHERE id_req=old.id;
else UPDATE marketing_credits_requests SET  statut=NEW.status WHERE id_req=old.id;
END IF;
end; |

[easy_katka]

galaxy, теперь ругается на последнию строку, я выполняю этот код в myphpadmin это не проблема?

[easy_katka]

galaxy, все ок решил проблему