MySQL — создать ошибку триггера 1064 (рядом с «DELIMITER;»)

Надеюсь, все хорошо.

Я пытаюсь создать триггер mysql, но продолжаю получать следующую ошибку:

[Err] 1064 — ошибка в синтаксисе SQL; проверьте руководство, соответствующее версии вашего сервера MySQL, для правильного синтаксиса для использования рядом с 'DELIMITER' в строке 1 [Err] DELIMITER ;

Код, который у меня есть, выглядит следующим образом (обратите внимание, что фактический файл имеет длину 130 строк, я только что включил часть, в которой проблема, или, по крайней мере, я так думаю).

/* BB Events Insert After Trigger */
DELIMITER $$
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW 
BEGIN 
    UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    IF NEW.`status_code` = 62465 THEN 
        INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
        UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1)  WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF; 
    IF NEW.`status_code` = 62467 THEN 
        UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
        UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF;
END;$$
DELIMITER ;


/* BB Journey Update After Trigger */
DELIMITER $$
CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
FOR EACH ROW 
BEGIN 
    IF NEW.`complete` <> 1 THEN 
        SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
        SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
    END IF;
    IF NEW.`complete` = 1 THEN 
        UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
    END IF;
END;$$
DELIMITER ;

Любая помощь высоко ценится!

Спасибо,

Смити :)


mysql sql triggers delimiter
person Smithey93    schedule 15.04.2014    source источник
comment
Какой инструмент вы используете для запуска этих операторов?   -  person a_horse_with_no_name    schedule 15.04.2014
comment
Попробуйте удалить ; после последнего КОНЦА   -  person Mihai    schedule 15.04.2014
comment
@a_horse_with_no_name Я использовал Navicat для выполнения файла .sql, хотя я собирался попробовать запустить его из командной строки...   -  person Smithey93    schedule 15.04.2014
comment
@Mihai Я только что попробовал это, он сказал, что в DELIMITER произошла ошибка;   -  person Smithey93    schedule 15.04.2014
comment
Таким образом, эти триггеры могут быть правильными, попробуйте каждый из них и изолируйте проблему.   -  person Mihai    schedule 15.04.2014
comment
Я нашел исправление, которое сработало, мне пришлось добавить DROP TRIGGER /*!50032 IF EXISTS */ journey_update_before $$ перед CREATE TRIGGER и изменить END$$ на END; $$. Я опубликую полный код в качестве ответа :)   -  person Smithey93    schedule 15.04.2014

Ответы (2)


arrow_upward
0
arrow_downward

Я понял это, посмотрев на Mysql create trigger 1064 error

Правильный код выглядит следующим образом:

/* BB Events Insert After Trigger */
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `event_insert_after` $$
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW 
BEGIN 
    UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    IF NEW.`status_code` = 62465 THEN 
        INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
        UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1)  WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF; 
    IF NEW.`status_code` = 62467 THEN 
        UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
        UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF;
END;
$$


/* BB Journey Update After Trigger */
DELIMITER $$
DROP TRIGGER /*!50032 IF EXISTS */ `journey_update_before` $$
CREATE TRIGGER `journey_update_before` BEFORE UPDATE ON `bb_journey`
FOR EACH ROW 
BEGIN 
    IF NEW.`complete` <> 1 THEN 
        SET NEW.`distance` = ROUND(NEW.`end_odometer`-NEW.`start_odometer`,2);
        SET NEW.`duration` = SEC_TO_TIME(NEW.`end_timestamp`-NEW.`start_timestamp`);
    END IF;
    IF NEW.`complete` = 1 THEN 
        UPDATE `bb_events` SET `journey_id` = NEW.`id` WHERE `timestamp` BETWEEN NEW.`start_timestamp` AND NEW.`end_timestamp`;
    END IF;
END;
$$

Я понятия не имею, как это работает, но это сработало. MySQL версии 5.5, CentOS 6 64 бит.

Спасибо всем :-)

person Smithey93    schedule 15.04.2014

arrow_upward
0
arrow_downward

попробуйте удалить ; перед END

DELIMITER $$
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW 
BEGIN 
    UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    IF NEW.`status_code` = 62465 THEN 
        INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
        UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1)  WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF; 
    IF NEW.`status_code` = 62467 THEN 
        UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
        UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF;
END $$                    -->removing ; 

   DELIMITER;

обновление:1

DELIMITER//;
CREATE TRIGGER `event_insert_after` AFTER INSERT ON `bb_events`
FOR EACH ROW 
BEGIN 
    UPDATE `bb_device` SET `latitude` = NEW.`latitude`, `longitude` = NEW.`longitude`, `status_code` = NEW.`status_code`, `timestamp` = NEW.`timestamp`, `speed` = NEW.`speed`, `driver_id` = NEW.`driver_id`, `heading` = NEW.`heading` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    IF NEW.`status_code` = 62465 THEN 
        INSERT INTO `bb_journey` (`account_id`, `device_id`, `start_timestamp`, `start_street`, `start_city`, `start_state`, `start_country`, `start_latitude`, `start_longitude`, `start_geozone`, `start_odometer`) VALUES (NEW.`account_id`, NEW.`device_id`, NEW.`timestamp`, NEW.`street`, NEW.`city`, NEW.`state`, NEW.`country`, NEW.`latitude`, NEW.`longitude`, NEW.`geozone_id`, NEW.`odometer`);
        UPDATE `bb_device` SET `current_journey` = (SELECT `id` FROM `bb_journey` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1)  WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF; 
    IF NEW.`status_code` = 62467 THEN 
        UPDATE `bb_journey` SET `end_timestamp` = NEW.`timestamp`, `end_street` = NEW.`street`, `end_city` = NEW.`city`, `end_state` = NEW.`state`, `end_country` = NEW.`country`, `end_latitude` = NEW.`latitude`, `end_longitude` = NEW.`longitude`, `end_geozone` = NEW.`geozone_id`, `end_odometer` = NEW.`odometer` WHERE `id` = (SELECT `current_journey` FROM `bb_device` WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id` ORDER BY `id` DESC LIMIT 1);
        UPDATE `bb_device` SET `current_journey` = '' WHERE `account_id` = NEW.`account_id` AND `device_id` = NEW.`device_id`;
    END IF;
END ; // 
DELIMITER;//
person jmail    schedule 15.04.2014